ShoppingList_Backend/sqlite_connector.php at master · aeibner/ShoppingList_Backend · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
<?php

    class DataBase{

        var $db;

        function __construct($args){
            $dbfile = $args['file'];
            try{
                $this->db = new SQLite3($dbfile, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);
            }catch(Exception $e){
                die(json_encode(array('type' => API_ERROR_DATABASE_CONNECT, 'content' => $e->getMessage())));
            }
            $resultQuery = $this->db->query("SELECT COUNT(*) as count FROM sqlite_master WHERE type='table' AND name='itemlist'");
            $row = $resultQuery->fetchArray();
            if($row['count'] == 0){
                $this->db->exec("CREATE TABLE itemlist(ITEM TEXT PRIMARY KEY NOT NULL, COUNT INT NOT NULL);");
            }
        }

        function __destructor(){
            $this->db->close();
        }

        function listall(){
            $resultQuery = $this->db->query("SELECT ITEM, COUNT FROM itemlist ORDER BY ITEM ASC;");
            $stack = array();
            if(!$resultQuery){
                return json_encode(array('type' => API_SUCCESS_LIST_EMPTY));
            }
            while($item = $resultQuery->fetchArray()){
                $itemData = array(
                    'itemTitle' => $item['ITEM'],
                    'itemCount' => $item['COUNT'],
					'checked' => false
                );
                array_push($stack, $itemData);
            }
            if(count($stack) == 0){
                return json_encode(array('type' => API_SUCCESS_LIST_EMPTY));
            }else{
                return json_encode(array('type' => API_SUCCESS_LIST, 'items' => $stack));
            }
        }

        function exists($item){
            $resultQuery = $this->db->query("SELECT COUNT(*) as count FROM itemlist WHERE ITEM = '".$item."';");
            $row = $resultQuery->fetchArray();
            if($row['count'] > 0){
                return True;
            }else{
                return False;
            }
        }

        function save($item, $count){
            $resultQuery = $this->db->query("INSERT INTO itemlist (ITEM, COUNT) VALUES('".$item."', ".$count.");");
            if($resultQuery){
                $result = json_encode(array('type' => API_SUCCESS_SAVE, 'content' => $item.' saved.'));
            }else{
                $result = json_encode(array('type' => API_ERROR_SAVE, 'content' => 'Saving failed'));
            }
            return $result;
        }

		function saveMultiple($jsonData){
			if(empty($jsonData)) {
					die(json_encode(array('type' => API_ERROR_MISSING_PARAMETER, 'content' => 'parameter missing for saveMultiple')));
				}
			//iterate over all items in json array
			$array = json_decode( $jsonData, true );
			foreach($array as $item)
			{
				$resultQuery = $this->db->query("INSERT INTO itemlist (ITEM, COUNT) VALUES('".$item['itemTitle']."', ".$item['itemCount'].");");
			}
            if($resultQuery){
                $result = json_encode(array('type' => API_SUCCESS_SAVE, 'content' => 'Multiple items saved'));
            }else{
                $result = json_encode(array('type' => API_ERROR_SAVE, 'content' => 'Saving failed'));
            }
            return $result;
        }

        function update($item, $count){
            $resultQuery = $this->db->query("UPDATE itemlist SET COUNT = ".$count." WHERE ITEM = '".$item."';");
            if($resultQuery){
                $result = json_encode(array('type' => API_SUCCESS_UPDATE, 'content' => $item.' updated.'));
            }else{
                $result = json_encode(array('type' => API_ERROR_UPDATE_, 'content' => 'Updating failed'));
            }
            return $result;
        }

		function deleteMultiple($jsonData){
			if(empty($jsonData)) {
					die(json_encode(array('type' => API_ERROR_MISSING_PARAMETER, 'content' => 'parameter missing for deleteMultiple')));
			}
			//iterate over all items in json array
			$array = json_decode( $jsonData, true );
			foreach($array as $item)
			{
				$resultQuery = $this->db->query("DELETE FROM itemlist WHERE ITEM = '".$item['itemTitle']."';");
			}
            if($resultQuery){
                $result = json_encode(array('type' => API_SUCCESS_DELETE, 'content' => 'Multiple items deleted'));
            }else{
                $result = json_encode(array('type' => API_ERROR_DELETE, 'content' => 'Deleting failed'));
            }
            return $result;
        }

        function delete($item){
            $resultQuery = $this->db->query("DELETE FROM itemlist WHERE ITEM = '".$item."';");
            if($resultQuery){
                $result = json_encode(array('type' => API_SUCCESS_DELETE, 'content' => $item.' deleted.'));
            }else{
                $result = json_encode(array('type' => API_ERROR_DELETE, 'content' => 'Deleting failed'));
            }
            return $result;
        }

        function clear(){
            $resultQuery = $this->db->query("DELETE FROM itemlist;");
            $this->db->exec("VACUUM;");
            if($resultQuery){
                $result = json_encode(array('type' => API_SUCCESS_CLEAR, 'content' => 'List cleared'));
            }else{
                $result = json_encode(array('type' => API_ERROR_CLEAR, 'content' => 'Clearing failed'));
            }
            return $result;
        }


    }

?>