Slide 1

Slide 1 text

More than SQL, but Less than ORM MoSQL (after v0.6)

Slide 2

Slide 2 text

Mosky 2

Slide 3

Slide 3 text

Mosky I'm working at Pinkoi 2

Slide 4

Slide 4 text

Mosky I'm working at Pinkoi COSCUP staff 2

Slide 5

Slide 5 text

Mosky I'm working at Pinkoi COSCUP staff Python trainer 2

Slide 6

Slide 6 text

Mosky I'm working at Pinkoi COSCUP staff Python trainer Speaker at COSCUP 2013, PyCon TW 2013, PyCon JP 2012, PyCon TW 2012 ... 2

Slide 7

Slide 7 text

Mosky I'm working at Pinkoi COSCUP staff Python trainer Speaker at COSCUP 2013, PyCon TW 2013, PyCon JP 2012, PyCon TW 2012 ... http://mosky.tw/ 2

Slide 8

Slide 8 text

Pinkoi.com   Builds  Design  Ecosystem for  people  to  BUY  /  SELL  /  SHARE  designs  and  to  be  INSPIRED.

Slide 9

Slide 9 text

Pinkoi.com   Builds  Design  Ecosystem Pinkoi  ͸ΞδΞͰ࠷΋େ͖͍σβΠϯγϣοϐϯά΢Σϒ αΠτͰ͢ɻ༏लͳσβΠφʔୡ͕͓٬͞ΜͷͨΊʹ͍ͭ ΋PinkoiͰҰ൪৽͍͠σβΠϯΛఏڙ͍ͯ͠·͢ɻૣΊʹ ͋ͳͨୡʹձ͍͍ͨͰ͢Ͷɻָ͓͠Έʂ

Slide 10

Slide 10 text

Outline 5

Slide 11

Slide 11 text

Outline Why not SQL? But ... 5

Slide 12

Slide 12 text

Outline Why not SQL? But ... Why ORM? But ... 5

Slide 13

Slide 13 text

Outline Why not SQL? But ... Why ORM? But ... MoSQL 5

Slide 14

Slide 14 text

Outline Why not SQL? But ... Why ORM? But ... MoSQL The Usage, Performance, and Security 5

Slide 15

Slide 15 text

Outline Why not SQL? But ... Why ORM? But ... MoSQL The Usage, Performance, and Security Demo 5

Slide 16

Slide 16 text

Doc: http://mosql.mosky.tw

Slide 17

Slide 17 text

Why not SQL?

Slide 18

Slide 18 text

Hard to Use 8

Slide 19

Slide 19 text

Hard to Use SELECT * FROM article LIMIT 1; 8

Slide 20

Slide 20 text

Hard to Use SELECT * FROM article LIMIT 1; add ORDER BY created? 8

Slide 21

Slide 21 text

Hard to Use SELECT * FROM article LIMIT 1; add ORDER BY created? add OFFSET 10? 8

Slide 22

Slide 22 text

Hard to Use SELECT * FROM article LIMIT 1; add ORDER BY created? add OFFSET 10? add GROUP BY author? 8

Slide 23

Slide 23 text

Hard to Use SELECT * FROM article LIMIT 1; add ORDER BY created? add OFFSET 10? add GROUP BY author? UPDATE article WHERE title='SQL' SET title='ORM'? 8

Slide 24

Slide 24 text

Hard to Use 9

Slide 25

Slide 25 text

Hard to Use Programming Error 9

Slide 26

Slide 26 text

Hard to Use Programming Error Programming Error 9

Slide 27

Slide 27 text

Hard to Use Programming Error Programming Error Programming Error 9

Slide 28

Slide 28 text

Hard to Use Programming Error Programming Error Programming Error !@#$ 9

Slide 29

Slide 29 text

May Be Injected 10

Slide 30

Slide 30 text

May Be Injected 'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs ) 10

Slide 31

Slide 31 text

May Be Injected 'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs ) Cracker can inject from value 10

Slide 32

Slide 32 text

May Be Injected 'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs ) Cracker can inject from value or identifier, actually. 10

Slide 33

Slide 33 text

May Be Injected 'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs ) Cracker can inject from value or identifier, actually. DON'T copy the code here! 10

Slide 34

Slide 34 text

It seems bad! But ...

Slide 35

Slide 35 text

SQL ... 12

Slide 36

Slide 36 text

SQL ... is fastest way to communicate with db, 12

Slide 37

Slide 37 text

SQL ... is fastest way to communicate with db, and everyone understands or learned it. 12

Slide 38

Slide 38 text

Why ORM?

Slide 39

Slide 39 text

Easy to Use 14

Slide 40

Slide 40 text

Easy to Use class Person(Base): __tablename__ = 'person' person_id = Column(String, primary_key=True) name = Column(String) ... 14

Slide 41

Slide 41 text

Easy to Use 15

Slide 42

Slide 42 text

Easy to Use mosky = Person('mosky', 'Mosky Liu') session.add(mosky) 15

Slide 43

Slide 43 text

Easy to Use mosky = Person('mosky', 'Mosky Liu') session.add(mosky) for person in session.query(Person).all(): print person.name, person.person_id 15

Slide 44

Slide 44 text

Easy to Use mosky = Person('mosky', 'Mosky Liu') session.add(mosky) for person in session.query(Person).all(): print person.name, person.person_id Let you forget the ugly SQL so far. 15

Slide 45

Slide 45 text

SQL Injection Free 16

Slide 46

Slide 46 text

SQL Injection Free Usually ORM guarantees it. 16

Slide 47

Slide 47 text

It seems good! But ...

Slide 48

Slide 48 text

ORM ... 18

Slide 49

Slide 49 text

ORM ... is slower, 18

Slide 50

Slide 50 text

ORM ... is slower, and you need to learn it from scratch. 18

Slide 51

Slide 51 text

ORM ... is slower, and you need to learn it from scratch. Sometimes it is just a black box. 18

Slide 52

Slide 52 text

SQL vs. ORM SQL ORM Easy-to-Use V Secure V Easy-to-Learn V Fast V

Slide 53

Slide 53 text

So ... MoSQL

Slide 54

Slide 54 text

The First Glance 21

Slide 55

Slide 55 text

The First Glance from mosql.query import select print select('person') 21

Slide 56

Slide 56 text

The First Glance from mosql.query import select print select('person') -> SELECT * FROM "person" 21

Slide 57

Slide 57 text

Map is just condition 22

Slide 58

Slide 58 text

Map is just condition select('person', { 'person_id': 'mosky' }) 22

Slide 59

Slide 59 text

Map is just condition select('person', { 'person_id': 'mosky' }) -> SELECT * FROM "person" WHERE "person_id" = 'mosky' 22

Slide 60

Slide 60 text

Sequence is just a list 23

Slide 61

Slide 61 text

Sequence is just a list select('person', select=('name', ) ) 23

Slide 62

Slide 62 text

Sequence is just a list select('person', select=('name', ) ) -> SELECT "name" FROM "person" 23

Slide 63

Slide 63 text

Map is also a set-list 24

Slide 64

Slide 64 text

Map is also a set-list insert('person', { 'person_id': 'mosky', 'name' : 'Mosky Liu' }) 24

Slide 65

Slide 65 text

Map is also a set-list insert('person', { 'person_id': 'mosky', 'name' : 'Mosky Liu' }) -> INSERT INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu') 24

Slide 66

Slide 66 text

Order doesn't matter 25

Slide 67

Slide 67 text

Order doesn't matter update('person', where={'person_id': 'mosky'}, set ={'name' : 'Mosky Liu'}, }) 25

Slide 68

Slide 68 text

Order doesn't matter update('person', where={'person_id': 'mosky'}, set ={'name' : 'Mosky Liu'}, }) -> UPDATE "person" SET "name" = 'Mosky Liu' WHERE "person_id" = 'mosky' 25

Slide 69

Slide 69 text

Operator also works! 26

Slide 70

Slide 70 text

Operator also works! select('person', { 'age >=': 20 }) 26

Slide 71

Slide 71 text

Operator also works! select('person', { 'age >=': 20 }) -> SELECT * FROM "person" WHERE "age" >= 20 26

Slide 72

Slide 72 text

All from the native data structures!

Slide 73

Slide 73 text

The Overview 28

Slide 74

Slide 74 text

The Overview insert(table, set, ...) 28

Slide 75

Slide 75 text

The Overview insert(table, set, ...) select(table, where, ...) 28

Slide 76

Slide 76 text

The Overview insert(table, set, ...) select(table, where, ...) update(table, where, set, ...) 28

Slide 77

Slide 77 text

The Overview insert(table, set, ...) select(table, where, ...) update(table, where, set, ...) delete(table, where, ...) 28

Slide 78

Slide 78 text

The Overview insert(table, set, ...) select(table, where, ...) update(table, where, set, ...) delete(table, where, ...) ... 28

Slide 79

Slide 79 text

If you like it,

Slide 80

Slide 80 text

sudo pip install mosql

Slide 81

Slide 81 text

Join is also available 31

Slide 82

Slide 82 text

Join is also available select( 'person', {'person_id': 'mosky'}, joins=left_join('detail',using=('person_id',)) ) 31

Slide 83

Slide 83 text

Join is also available select( 'person', {'person_id': 'mosky'}, joins=left_join('detail',using=('person_id',)) ) -> SELECT * FROM "person" LEFT JOIN "detail" USING ("person_id") WHERE "person_id" = 'mosky' 31

Slide 84

Slide 84 text

A Partial Query 32

Slide 85

Slide 85 text

A Partial Query fixed_args = {'table': 'person'} person_select = select.breed(fixed_args) person_select() 32

Slide 86

Slide 86 text

A Partial Query fixed_args = {'table': 'person'} person_select = select.breed(fixed_args) person_select() -> SELECT * FROM "person" 32

Slide 87

Slide 87 text

A Partial Query fixed_args = {'table': 'person'} person_select = select.breed(fixed_args) person_select() -> SELECT * FROM "person" select('person') 32

Slide 88

Slide 88 text

A Partial Query fixed_args = {'table': 'person'} person_select = select.breed(fixed_args) person_select() -> SELECT * FROM "person" select('person') -> SELECT * FROM "person" 32

Slide 89

Slide 89 text

Performance 33

Slide 90

Slide 90 text

Performance About 4x faster than SQLAlchemy. 33

Slide 91

Slide 91 text

Performance About 4x faster than SQLAlchemy. Just a little bit slower than pure SQL. 33

Slide 92

Slide 92 text

Security 34

Slide 93

Slide 93 text

Security Security by default. 34

Slide 94

Slide 94 text

Security Security by default. Use escaping technique. 34

Slide 95

Slide 95 text

Security Security by default. Use escaping technique. Prevent SQL injection from both value and identifier. 34

Slide 96

Slide 96 text

Security Security by default. Use escaping technique. Prevent SQL injection from both value and identifier. Passed the tests from sqlmap at level=5 and risk=3. 34

Slide 97

Slide 97 text

SQL vs. ORM SQL ORM Easy-to-Use V Secure V Easy-to-Learn V Fast V

Slide 98

Slide 98 text

SQL < ______ < ORM SQL ORM Easy-to-Use V Secure V Easy-to-Learn V Fast V

Slide 99

Slide 99 text

SQL < MoSQL < ORM SQL MoSQL ORM Easy-to-Use V V Secure V V Easy-to-Learn V V Fast V V

Slide 100

Slide 100 text

Demo

Slide 101

Slide 101 text

Demo 39

Slide 102

Slide 102 text

Demo Arbitrary Query with Web 39

Slide 103

Slide 103 text

Demo Arbitrary Query with Web Serious Usage using Class 39

Slide 104

Slide 104 text

Demo Arbitrary Query with Web Serious Usage using Class All the code are in the Github! 39

Slide 105

Slide 105 text

The End

Slide 106

Slide 106 text

The End 41

Slide 107

Slide 107 text

The End MoSQL is ... 41

Slide 108

Slide 108 text

The End MoSQL is ... Easy-to-Use 41

Slide 109

Slide 109 text

The End MoSQL is ... Easy-to-Use Easy-to-Learn 41

Slide 110

Slide 110 text

The End MoSQL is ... Easy-to-Use Easy-to-Learn Secure 41

Slide 111

Slide 111 text

The End MoSQL is ... Easy-to-Use Easy-to-Learn Secure Fast 41

Slide 112

Slide 112 text

The End MoSQL is ... Easy-to-Use Easy-to-Learn Secure Fast sudo pip install mosql 41

Slide 113

Slide 113 text

The End MoSQL is ... Easy-to-Use Easy-to-Learn Secure Fast sudo pip install mosql http://mosql.mosky.tw/ 41