postmodern postgresql application development - Net Super Brain

hornbeastcalmΔιαχείριση Δεδομένων

27 Νοε 2012 (πριν από 4 χρόνια και 9 μήνες)

221 εμφανίσεις

PostgreSQL Conference East 08
POSTMODERN POSTGRESQL
APPLICATION DEVELOPMENTBY
DAVID SANKEL @
S SA AN NK KE EL L S SO OF FT TW WA AR RE E @ @
SANKELSOFTWARE.COMGOALS
O Op pe en n S So ou ur rc ce e
S Siim mp ple le D De ep plo loy ym me en nt t
P Pla lat tf fo or rm m
In Ind de ep pe en nd de en nc ce e
Concrete
Perspectives
G Go oo od d L Lo oo ok kiin ng g
Abstract
E Ea as sy y t to o G Gr ra as sp p
F Fu un nFRAMEWORKTHE JOURNEY
‘‘m ma ak ke e d da at ta a’’ Design It Glue It Together Put it in a Box
‘‘m ma ak ke e d da at ta a’’
M M M Ma a a ak k k ke e e ef f f fiiiilllle e e e
sql/createDatabase.sql
sql/createTables.sql
sql/addExampleData.sqlGNU MAKE ROCKS!
DATABASE_NAME=customers
PSQL = psql --username postgres --quiet
database:
$ $( (P PS SQ QL L) ) -- --d db bn na am me e t te em mp plla at te e1 1 \ \
--variable database=$(DATABASE_NAME) \
--file sql/createDatabase.sql
$(PSQL) --dbname $(DATABASE_NAME) \
--file sql/createTables.sql
$(PSQL) --dbname $(DATABASE_NAME) \
--file sql/addExampleData.sqlSQL/CREATEDATABASE.SQL
DROP DATABASE :database;
\set ON_ERROR_STOP
CREATE DATABASE :database;
\ \u un ns se et t O ON N_ _E ER RR RO OR R_ _S ST TO OP PSQL/CREATETABLES.SQL
No fussing!
SET client_min_messages TO WARNING;
CREATE TABLE customers (
"id" SERIAL PRIMARY KEY,
"businessName" text NOT NULL DEFAULT '',
" "w we eb bs siit te e" " t te ex xt t N NO OT T N NU UL LL L D DE EF FA AU UL LT T '''',,
"businessAddress" text NOT NULL DEFAULT '',
"businessPhone" text NOT NULL DEFAULT '',
"contactName" text NOT NULL DEFAULT '',
"contactPhone" text NOT NULL DEFAULT '',
"notes" text NOT NULL DEFAULT ''
);SQL/ADDEXAMPLEDATA.SQL
INSERT INTO customers
("businessName", "contactName")
VALUES ('Sans Enterprises', 'Joe Smith');
INSERT INTO customers
( (" "b bu us siin ne es ss sN Na am me e" ",, " "c co on nt ta ac ct tN Na am me e" ") )
VALUES ('Watermelon Labs', 'Eric Hendricson');
\set lastId currval(’customers_id_seq')
Advanced
\set photo `python util/fileToSqlString.py
sql/photo.jpg`THE JOURNEY
‘‘m ma ak ke e d da at ta a’’ Design It Glue It Together Put it in a Box
‘‘m ma ak ke e d da at ta a’’
M M M Ma a a ak k k ke e e ef f f fiiiilllle e e e
sql/createDatabase.sql
sql/createTables.sql
sql/addExampleData.sqlTHE JOURNEY
‘make data’ D De es siig gn n IIt t Glue It Together Put it in a Box
D De es siig gn n IIt t
Q Q Q Qt t t t D D D De e e es s s siiiig g g gn n n ne e e er r r r
Forms/CustomerBrowser.ui
Forms/CustomerWidget.uiQT DESIGNEROUR FORMSTHE JOURNEY
‘make data’ D De es siig gn n IIt t Glue It Together Put it in a Box
D De es siig gn n IIt t
Q Q Q Qt t t t D D D De e e es s s siiiig g g gn n n ne e e er r r r
Forms/CustomerBrowser.ui
Forms/CustomerWidget.uiTHE JOURNEY
‘make data’ Design It G Gllu ue e IIt t T To og ge et th he er r Put it in a Box
G Gllu ue e IIt t T To og ge et th he er r
P P P Py y y yt t t th h h ho o o on n n n
S S S SQ Q Q QL L L LA A A Allllc c c ch h h he e e em m m my y y y
P Py yQ Qt t
P Py yQ Qt t
*.pyPYTHON
d d d de e e ef f f f getLinks( html ):
"""Returns a list of http:// URLS found in the passed html text"""
from HTMLParser import HTMLParser
c clla as ss s LinkSucker( HTMLParser ):
c clla as ss s
d d d de e e ef f f f __init__( self ):
HTMLParser.__init__(self)
self.links = []
d d d de e e ef f f f handle_starttag( self, tag, attrs ):
iif f tag != 'a':
iif f
r r r re e e et t t tu u u ur r r rn n n n
f f f fo o o or r r r (name, value) iiiin n n n attrs:
iiiif f f f name == "href":
self.links.append( value )
t tr ry y:
t tr ry y
linkSucker = LinkSucker()
linkSucker.feed( html )
linkSucker.close()
r r r re e e et t t tu u u ur r r rn n n n linkSucker.links
e ex xc ce ep pt t:
e ex xc ce ep pt t
r r r re e e et t t tu u u ur r r rn n n n []SQL: THE RIGHT LANGUAGE FOR THE JOB?
S Se et t t th he e c cu us st to om me er r’s ’s b bu us siin ne es ss s n na am me e t to o “ “B Biilllly y B Bo ob b” ”
S Se et t t th he e c cu us st to om me er r’s ’s b bu us siin ne es ss s n na am me e t to o “ “B Biilllly y B Bo ob b” ”
No!
UPDATE customers SET “businessName” = ‘Billy
Bob’ WHERE id = 23
c cu ur rs so or r.e .ex xe ec cu ut te e( (
Hell No!
“””UPDATE customers
SET “businessName” = %(businessName)s
WHERE id = %(id)s “””,
{ “businessName”:“Billy Bob”, “id”:23 } )
customer.businessName = “Billy Bob”
Yes!SQLALCHEMY
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import sessionmaker, mapper
engine = create_engine( 'postgres://postgres@localhost/customers' )
m me et ta ad da at ta a = = M Me et ta aD Da at ta a( ( b bin ind d= =e en ng gin ine e,, r re ef flle ec ct t= =T Tr ru ue e) )
Session = sessionmaker(bind=engine, autoflush=True,
transactional=True)
class Customer(object): pass
mapper( Customer, Table('customers', metadata ) )SQLALCHEMY
session = Session()
customer = Customer( businessName=“Jamb Safety”,
website=“www.jamb.com” )
session.save( customer )
f fo or r c cu us st to om me er r iin n S Se es ss siio on n.q .qu ue er ry y( (C Cu us st to om me er r) ).f .fiillt te er r( (
Customer.businessName.like(“Jamb%”)):
print customer.businessName
session.commit()MORE SQLALCHEMY
 C C C Co o o om m m mp p p plllle e e ex x x x O O O Ob b b bjjjje e e ec c c ct t t t Q Q Q Qu u u ue e e er r r riiiie e e es s s s/ / / /R R R Re e e elllla a a at t t tiiiio o o on n n ns s s s
session.query(Customer).join('invoices')
.filter(Invoice.date=="2008-03-14").all()
customer.invoices[0].date
invoice.customer.contactPhone
 A A A A A A A A f f f f f f f fu u u u u u u ullllllllllllllll e e e e e e e ex x x x x x x xp p p p p p p pr r r r r r r re e e e e e e es s s s s s s ss s s s s s s siiiiiiiio o o o o o o on n n n n n n n lllllllla a a a a a a an n n n n n n ng g g g g g g gu u u u u u u ua a a a a a a ag g g g g g g ge e e e e e e e
s = select([(customers.c.businessName + ", " +
customers.c.website).label('title')],
and_(
invoices.c.customerId==customers.c.id,
invoices.c.date ==“2008-03-14”
))
 P P P Po o o oo o o olllliiiin n n ng g g gPYQT
from PyQt4 import QtCore, QtGui, uic
import webbrowser
FormClass, BaseClass = uic.loadUiType("forms/CustomerWidget.ui")
c c c clllla a a as s s ss s s s CustomerWidget(BaseClass, FormClass):
d d d d d d d de e e e e e e ef f f f f f f f _ __ _in iniit t_ __ _( (s se ellf f) )::
BaseClass.__init__(self)
self.setupUi(self)
@QtCore.pyqtSignature("")
d d d de e e ef f f f on_websitePushButton_clicked( self ):
website = str( self.websiteLineEdit.text() )
webbrowser.open( website )FILL IN THE CUSTOMER WIDGET
c c c clllla a a as s s ss s s s CustomerWidget(BaseClass, FormClass):
#...
d d d de e e ef f f f fromItem( self, customer ):
iif f( customer.id ):
iif f
self.idLineEdit.setText( str(customer.id) )
self.businessNameLineEdit.setText( customer.businessName )
s se ellf f..b bu us sin ine es ss sP Ph ho on ne eL Lin ine eE Ed diit t..s se et tT Te ex xt t( ( c cu us st to om me er r..b bu us sin ine es ss sP Ph ho on ne e ) )
#...
d de ef f toItem( self, customer ):
d de ef f
customer.businessName = unicode( self.businessNameLineEdit.text()
)
customer.businessPhone = unicode(
self.businessPhoneLineEdit.text() )
customer.contactName = unicode( self.contactNameLineEdit.text() )
customer.contactPhone = unicode( self.contactPhoneLineEdit.text() )
#...
d d d de e e ef f f f clear( self ):FILL IN THE DATABROWSER
c c c clllla a a as s s ss s s s DataBrowser(BaseClass, FormClass):
d de ef f __init__(self, Item, ItemWidget):
d de ef f
d d d de e e ef f f f load(self):
BaseClass.__init__(self)
self.itemWidget.fromItem(
self.setupUi(self)
self.items[self.index] )
self.state = self.BROWSE
self.Item = Item
@ @Q Qt tC Co or re e..p py yq qt tS Siig gn na at tu ur re e( (" "" ") )
layout = QtGui.QVBoxLayout()
d d d de e e ef f f f on_nextPushButton_clicked( self ):
layout.setSpacing(0)
iiiif f f f self.index + 1 >= self.items.count():
self.itemFrame.setLayout(layout)
r re et tu ur rn n self.itemWidget.toItem(
r re et tu ur rn n
self.itemWidget = ItemWidget()
self.items[self.index] )
layout.addWidget( self.itemWidget )
self.index += 1
self.load()
self.session = Session()
self.items = self.session.query(Item)
SELECT
self.index = 0
count(customers.id)
self.load()
FROM customersSOMETHING TO RUN
from PyQt4 import QtGui
run:
from DataBrowser import DataBrowser
python main.py
from Customer import Customer
from CustomerWidget import CustomerWidget
import sys
d d d d d d d de e e e e e e ef f f f f f f f r ru un n( () )::
a ap pp p = = Q Qt tG Gu uii..Q QA Ap pp plliic ca at tiio on n( (s sy ys s..a ar rg gv v) )
widget = DataBrowser(Customer, CustomerWidget)
widget.show()
widget.setWindowTitle("Customers 1.0")
status = app.exec_()
sys.exit(status)
iiiif f f f __name__ == "__main__":
run()THE JOURNEY
‘make data’ Design It G Gllu ue e IIt t T To og ge et th he er r Put it in a Box
G Gllu ue e IIt t T To og ge et th he er r
P P P Py y y yt t t th h h ho o o on n n n
S S S SQ Q Q QL L L LA A A Allllc c c ch h h he e e em m m my y y y
S S S SQ Q Q QL L L LA A A Allllc c c ch h h he e e em m m my y y y
P P P Py y y yQ Q Q Qt t t t
*.pyFRAMEWORKTHE JOURNEY
‘make data’ Design It Glue It Together P Pu ut t iit t iin n a a B Bo ox x
P Pu ut t iit t iin n a a B Bo ox x
p p p py y y y2 2 2 2e e e ex x x xe e e e
N N N NS S S SIIIIS S S SPY2EXE
from distutils.core import setup
import py2exe
import glob
setup(
name="Customers",
a au ut th ho or r= =" "S Sa an nk ke ell S So of ft tw wa ar re e" ",,
author_email="david@sankelsoftware.com",
url="http://sankelsoftware.com",
license=“GPL",
version=“1.0.0",
windows=[ { "script":"main.py“,}],
options={"py2exe":{"includes":["sip”]}},
data_files=[
("forms",glob.glob("forms/*.ui")),
] )
release:
python setup.py py2exe --quiet --dist-dir=distNSIS
S S S Se e e ec c c ct t t tiiiio o o on n n n "Customer Program" SecCustomerProgram
SetOutPath $INSTDIR File /r "dist\“
; Write the uninstall keys for Windows…
WriteRegStr HKLM "Software\Microsoft\..."
"DisplayName" "Customers“
WriteRegStr HKLM "Software\Microsoft\... "
" "D Diis sp plla ay yV Ve er rs siio on n" " " "1 1..0 0..0 0“ “
; Add shortcut to the Desktop…
CreateShortCut "$DESKTOP\Customers.lnk"
"$INSTDIR\main.exe" "" "$INSTDIR\main.exe" 0
S S S Se e e ec c c ct t t tiiiio o o on n n nE E E En n n nd d d d
S S S Se e e ec c c ct t t tiiiio o o on n n n "Uninstall“
DeleteRegKey HKLM "Software\Microsoft\...“
RMDir /r "$INSTDIR\“
Delete "$DESKTOP\Customers.lnk“
S S S Se e e ec c c ct t t tiiiio o o on n n nE E E En n n nd d d dNSIS POSTGRESQL!
S S S Se e e ec c c ct t t tiiiio o o on n n n /o "Customer Database Server" SecServer
; Install PostgreSQL
SetOutPath $TEMP
File "support\postgresql-8.3-int.msi“
ReadEnvStr $1 "COMPUTERNAME“
execwait 'msiexec /i "$TEMP\postgresql-8.3-int.msi" /qr INTERNALLAUNCH=1
C CR RE EA AT TE ES SE ER RV VIIC CE EU US SE ER R= =" "1 1” ” S SE ER RV VIIC CE ED DO OM MA AIIN N= =" "$ $1 1" "
SERVICEPASSWORD="12345" SUPERPASSWORD="12345“ ‘
SetOutPath $PROGRAMFILES\PostgreSQL\8.3\data
File "server\pg_hba.conf“
File "server\postgresql.conf“
; Reload the configuration files
ExecWait '"$PROGRAMFILES\PostgreSQL\8.3\bin\pg_ctl.exe" reload -D
"$PROGRAMFILES\PostgreSQL\8.3\data\“’
; Set up initial tables and data…MAKE IT
NSIS=NSISDIR="C:\…\\NSIS"
NSISCONFDIR="C:…\\NSIS"
"C:\\...\\makensis.exe"
release:
python setup.py py2exe --quiet --dist-dir=dist
$(NSIS) customers.nsiTHE JOURNEY
‘make data’ Design It Glue It Together Put it in a BoxBY
DAVID SANKEL @
SANKEL SOFTWARE @
SANKELSOFTWARE.COM
PostgreSQL Conference East 08
POSTMODERN POSTGRESQL
APPLICATION DEVELOPMENT
Presentation and Source Available at netsuperbrain.com