24PG4G6C

1import mysql.connector
2
3database = mysql.connector.connect(
4            user = maps,
5            password = pwd,
6            host = args.connect
7    )
8
9    #SETUP DATABASE AND PROCEDURES DEFINITIONS FOR FUNCTIONS BELOW
10    cursor = setup_database(database, args)
11    setup_procedures(cursor, args.table, database)
12	
13import mysql.connector
14
15def setup_database(database, args):
16    #CREATING DATABASE
17    cursor = database.cursor()
18
19    cursor.execute('DROP DATABASE IF EXISTS ' + args.db)
20    database.commit()
21
22    cursor.execute('CREATE DATABASE ' + args.db
23            + ' CHARACTER SET ' + args.char + ' COLLATE ' + args.collation
24            )
25    database.commit()
26
27    cursor.execute('USE ' + args.db)
28    database.commit()
29
30    cursor.execute('DROP TABLE IF EXISTS ' + args.table)
31    database.commit()
32
33    #MAXIMUM SIZE OF TABLE = 64 TERRABYTES, SHOULD BE ENOUGH...
34    cursor.execute('CREATE TABLE ' + args.table + ''' (
35                        content_id BIGINT AUTO_INCREMENT PRIMARY KEY,
36                        content TEXT NOT NULL,
37                        lft BIGINT NOT NULL,
38                        rgt BIGINT NOT NULL
39                    )ENGINE=InnoDB'''
40                  )
41    database.commit()
42    return cursor;
43	
44import mysql.connector
45
46__all__ = ['setup_procedures']
47
48def setup_procedures(cursor, table, database):
49    #ADD NODE adds node to specified parent and returns childs_id
50
51    cursor.execute('''
52                DROP PROCEDURE IF EXISTS addnode;
53
54                CREATE DEFINER = CURRENT_USER PROCEDURE addnode (
55                    IN parent_id BIGINT,
56                    IN text_content TEXT
57                )
58
59                BEGIN
60
61                DECLARE parent_lft BIGINT DEFAULT NULL;
62                DECLARE parent_rgt BIGINT DEFAULT NULL;
63                DECLARE temp BIGINT DEFAULT NULL;
64
65                SELECT content_id, lft, rgt INTO parent_id, parent_lft, parent_rgt
66                FROM '''+ table +
67            ''' WHERE content_id = parent_id;
68
69                -- inserting node:
70
71                START TRANSACTION;
72
73                    UPDATE ''' + table + ''' SET lft = CASE WHEN lft >  parent_rgt THEN lft + 2 ELSE lft END
74                                    ,rgt = CASE WHEN rgt >= parent_rgt THEN rgt + 2 ELSE rgt END
75                    WHERE rgt >= parent_rgt;
76
77                    INSERT INTO ''' + table + ''' (content, lft, rgt)
78                         VALUES (text_content, parent_rgt , parent_rgt + 1);
79
80                    SELECT LAST_INSERT_ID() INTO temp;
81
82                COMMIT;
83
84                -- return value
85
86                SELECT temp AS child_id;
87
88                END
89                ''',
90                multi=True
91    )
92    database.commit()
93	
94#id is int, root is xml object, root.text is string, cursor is database's #cursor
95    def recursive_read(root, id, cursor):
96        if root.text is not None:
97            cursor.callproc('addnode', (id, root.text))
98            id+=1
99        for elem in root.getchildren():
100            recursive_read(elem, id, cursor)