MoSQL: More than SQL, but Less than ORM

Slide 1

Slide 1 text

1 MoSQL MoSQL Mosky Mosky

Slide 2

Slide 2 text

2 More than SQL, but Less than ORM More than SQL, but Less than ORM MoSQL MoSQL

Slide 3

Slide 3 text

3 Outline Outline ● Why not SQL? Why not SQL? ● Why ORM? Why ORM? ● MoSQL MoSQL – SQL Builders SQL Builders – Model of Result Set Model of Result Set ● Conclusion Conclusion

Slide 4

Slide 4 text

4 Why not SQL? Why not SQL?

Slide 5

Slide 5 text

5 SQL Syntax SQL Syntax ● SELECT * FROM article; SELECT * FROM article; ● SELECT * FROM article LIMIT 1; SELECT * FROM article LIMIT 1; ● add “ ORDER BY created ”? add “ ORDER BY created ”? ● add “ OFFSET 10 ”? add “ OFFSET 10 ”? ● add “ GROUP BY author ”? add “ GROUP BY author ”? ● Is “ UPDATE article WHERE title='SQL' SET Is “ UPDATE article WHERE title='SQL' SET title='ORM' ” correct? title='ORM' ” correct?

Slide 6

Slide 6 text

6 !@#$% !@#$%

Slide 7

Slide 7 text

7 SQL Injection SQL Injection ● ') or '1'='1 ') or '1'='1 ● ' or true; -- ' or true; -- ● ' or 1=1; -- ' or 1=1; -- ● ' or 2=2; -- ' or 2=2; -- ● ' or 'str'='str'; -- ' or 'str'='str'; -- ● … …

Slide 8

Slide 8 text

8 It may be hacker friendly. It may be hacker friendly.

Slide 9

Slide 9 text

9 SQL seems ancient, but ... SQL seems ancient, but ...

Slide 10

Slide 10 text

10 using SQL is the using SQL is the FASTEST FASTEST way. way.

Slide 11

Slide 11 text

11 Why ORM? Why ORM?

Slide 12

Slide 12 text

12 ORM Syntax ORM Syntax class User(Base): class User(Base): __tablename__ = 'users' __tablename__ = 'users' name = Column(String) name = Column(String) fullname = Column(String) fullname = Column(String) password = Column(String) password = Column(String)

Slide 13

Slide 13 text

13 ORM Syntax (cont.) ORM Syntax (cont.) >>> fake_user = User('fakeuser', 'Invalid', >>> fake_user = User('fakeuser', 'Invalid', '12345') '12345') >>> session.add(fake_user) >>> session.add(fake_user) >>> for row in session.query(User, >>> for row in session.query(User, User.name).all(): User.name).all(): ... print row.User, row.name ... print row.User, row.name

Slide 14

Slide 14 text

14 hmmm … hmmm …

Slide 15

Slide 15 text

15 SQL Injection SQL Injection ● ' or true; -- ' or true; -- ● ' or 1=1; -- ' or 1=1; -- ● ' or 1=1; # ' or 1=1; # ● ' or 1=1; /* ' or 1=1; /* ● ') or '1'='1 ') or '1'='1 ● … … ● Safer Safer

Slide 16

Slide 16 text

16 It's good! It's good!

Slide 17

Slide 17 text

17 ORM seems modern, but ... ORM seems modern, but ...

Slide 18

Slide 18 text

18 the most of ORMs are SLOW. the most of ORMs are SLOW.

Slide 19

Slide 19 text

19 SQL < ______ < ORM SQL < ______ < ORM

Slide 20

Slide 20 text

20 SQL < MoSQL < ORM SQL < MoSQL < ORM

Slide 21

Slide 21 text

21 SQL Builders SQL Builders

Slide 22

Slide 22 text

22 SQL Builders (cont.) SQL Builders (cont.) >>> from mosql.build import * >>> from mosql.build import * >>> >>> select('pycon') select('pycon') SELECT * FROM "pycon" SELECT * FROM "pycon" >>> select('pycon', >>> select('pycon', {'id': 'mosky'} {'id': 'mosky'}) ) SELECT * FROM "pycon" WHERE "id" = 'mosky' SELECT * FROM "pycon" WHERE "id" = 'mosky'

Slide 23

Slide 23 text

23 SQL Builders (cont.) SQL Builders (cont.) >>> insert('pycon', >>> insert('pycon', {'yr': 2013, 'id': 'masky'} {'yr': 2013, 'id': 'masky'}) ) INSERT INTO "pycon" ("id", "yr") VALUES ('masky', 2013) INSERT INTO "pycon" ("id", "yr") VALUES ('masky', 2013) >>> update('pycon', >>> update('pycon', ... ... where={'id': 'masky'} where={'id': 'masky'}, , ... ... set ={'id': 'mosky'} set ={'id': 'mosky'} ... ) ... ) UPDATE "pycon" SET "id"='mosky' WHERE "id" = 'masky' UPDATE "pycon" SET "id"='mosky' WHERE "id" = 'masky'

Slide 24

Slide 24 text

24 SQL Builders (cont.) SQL Builders (cont.) ● insert(table, insert(table, set set, …) , …) ● select(table, select(table, where where, …) , …) ● update(table, update(table, where where, , set set, …) , …) ● delete(table, delete(table, where where, …) , …) ● ... ...

Slide 25

Slide 25 text

25 If you like it, If you like it,

Slide 26

Slide 26 text

26 sudo pip install mosql sudo pip install mosql

Slide 27

Slide 27 text

27 Model of Result Set Model of Result Set

Slide 28

Slide 28 text

28 Model: Configure Connection Model: Configure Connection import psycopg2.pool import psycopg2.pool from mosql.result import Model from mosql.result import Model pool = psycopg2.pool.SimpleConnectionPool(1, 5, pool = psycopg2.pool.SimpleConnectionPool(1, 5, database='mosky') database='mosky') class PostgreSQL(Model): class PostgreSQL(Model): getconn = pool.getconn getconn = pool.getconn putconn = pool.putconn putconn = pool.putconn

Slide 29

Slide 29 text

29 Model: Set the Name of Table Model: Set the Name of Table class Person(PostgreSQL): class Person(PostgreSQL): table = 'person' table = 'person' >>> Person.select( >>> Person.select({'person_id': 'mosky'} {'person_id': 'mosky'}) ) {'name': ['Mosky Liu'], 'person_id': ['mosky']} {'name': ['Mosky Liu'], 'person_id': ['mosky']} >>> Person.where(person_id= >>> Person.where(person_id=('andy', 'mosky') ('andy', 'mosky')) ) {'name': ['Andy Warhol', 'Mosky Liu'], 'person_id': {'name': ['Andy Warhol', 'Mosky Liu'], 'person_id': ['andy', 'mosky']} ['andy', 'mosky']}

Slide 30

Slide 30 text

30 Model: Make Queries Model: Make Queries Person. Person.select select({'person_id': 'mosky'}) ({'person_id': 'mosky'}) Person. Person.insert insert({'person_id': 'tina'}) ({'person_id': 'tina'}) Person. Person.update update( ( where={'person_id': 'mosky'}, where={'person_id': 'mosky'}, set ={'name' : 'Yiyu Liu'} set ={'name' : 'Yiyu Liu'} ) ) Person. Person.delete delete({'person_id': 'tina'}) ({'person_id': 'tina'})

Slide 31

Slide 31 text

31 Model: Squash Columns Model: Squash Columns class Person(PostgreSQL): class Person(PostgreSQL): table = 'person' table = 'person' squashed = set(['person_id', 'name']) squashed = set(['person_id', 'name']) >>> Person.select({'person_id': 'mosky'}) >>> Person.select({'person_id': 'mosky'}) {'name': {'name': 'Mosky Liu' 'Mosky Liu', 'person_id': , 'person_id': 'mosky' 'mosky'} } >>> Person.where(person_id=('andy', 'mosky')) >>> Person.where(person_id=('andy', 'mosky')) {'name': {'name': 'Andy Warhol' 'Andy Warhol', 'person_id': , 'person_id': 'andy' 'andy'} }

Slide 32

Slide 32 text

32 Model: Arrange Model: Arrange class Person(PostgreSQL): class Person(PostgreSQL): ... ... arrange_by = ('person_id', ) arrange_by = ('person_id', ) >>> for person in Person.arrange( >>> for person in Person.arrange({'person_id': {'person_id': ('andy', 'mosky')} ('andy', 'mosky')}): ): ... print person ... print person {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Mosky Liu', 'person_id': 'mosky'} {'name': 'Mosky Liu', 'person_id': 'mosky'}

Slide 33

Slide 33 text

33 Model: Arrange (cont.) Model: Arrange (cont.) >>> for detail in >>> for detail in Detail Detail.arrange({'person_id': .arrange({'person_id': ('mosky', 'andy')}): ('mosky', 'andy')}): ... print detail ... print detail ... ... {'detail_id': [5], {'detail_id': [5], 'key': 'email', 'key': 'email', 'person_id': 'andy', 'person_id': 'andy', 'val': ['andy@gmail.com']} 'val': ['andy@gmail.com']} ... ...

Slide 34

Slide 34 text

34 Model: Find Model: Find class Person(PostgreSQL): class Person(PostgreSQL): ... ... arrange_by = ('person_id', ) arrange_by = ('person_id', ) >>> for person in Person. >>> for person in Person.find find( (person_id=('andy', person_id=('andy', 'mosky') 'mosky')): ): ... print person ... print person {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Mosky Liu', 'person_id': 'mosky'} {'name': 'Mosky Liu', 'person_id': 'mosky'}

Slide 35

Slide 35 text

35 Model: Identify a Row Model: Identify a Row class Person(PostgreSQL): class Person(PostgreSQL): ... ... ident_by = ('person_id', ) ident_by = ('person_id', )

Slide 36

Slide 36 text

36 Model: Modification Model: Modification >>> p = Person.where(person_id='mosky') >>> p = Person.where(person_id='mosky') >>> >>> p['name'] = 'Yiyu Liu' p['name'] = 'Yiyu Liu' >>> >>> p.name = 'Yiyu Liu' p.name = 'Yiyu Liu' >>> p.save() >>> p.save() >>> d = >>> d = Detail Detail.where( .where(person_id='mosky', key='email' person_id='mosky', key='email') ) >>> >>> p['val'][0] = '' p['val'][0] = '' >>> >>> p.val[0] = '' p.val[0] = '' >>> p.save() >>> p.save()

Slide 37

Slide 37 text

37 Model: Pop and Append Model: Pop and Append >>> d = Detail.where( >>> d = Detail.where(person_id='mosky', key='email' person_id='mosky', key='email') ) >>> >>> p.pop(-1) p.pop(-1) >>> >>> p.append({'val': ''}) p.append({'val': ''}) >>> p.save() >>> p.save()

Slide 38

Slide 38 text

38 Model: Default Clauses Model: Default Clauses class Person(PostgreSQL): class Person(PostgreSQL): ... ... clauses = dict( clauses = dict( order_by=('person_id', ) order_by=('person_id', ) ) )

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

41 Conclusion Conclusion ● Easy-to-Learn Easy-to-Learn ● Convenient Convenient ● Faster Faster ● Secure Secure ● sudo pip install mosql sudo pip install mosql ● http://mosql.mosky.tw/ http://mosql.mosky.tw/ ● Welcome to fork! Welcome to fork!