-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path_sql_install.txt
316 lines (277 loc) · 11 KB
/
_sql_install.txt
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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
-- phpMyAdmin SQL Dump
-- version 3.5.4
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jul 26, 2013 at 09:30 PM
-- Server version: 5.1.61-log
-- PHP Version: 5.3.14
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
CREATE TABLE IF NOT EXISTS `devices` (
`device_id` smallint(6) NOT NULL AUTO_INCREMENT,
`id` varchar(50) COLLATE utf8_latvian_ci DEFAULT NULL COMMENT 'id, uzlīme uz datora',
`description` text COLLATE utf8_latvian_ci COMMENT 'apraksts, arī tehniskais.',
`hostname` varchar(254) COLLATE utf8_latvian_ci DEFAULT NULL COMMENT 'nosaukums',
`owner_id` smallint(6) DEFAULT NULL COMMENT 'īpašnieka ID no tabulas party',
`invoice_id` smallint(6) DEFAULT NULL COMMENT 'Arējā atslēga uz tabulu invoices, invoice_id',
`user_id` smallint(6) NOT NULL,
`device_type` smallint(6) NOT NULL COMMENT '1=computer, 2=monitor, 3=UPS, 4=mobile,5=Keyboard,6=Mouse',
`inv_id` smallint(6) DEFAULT NULL,
`konts` smallint(6) NOT NULL,
`status` smallint(6) DEFAULT '0' COMMENT '1=OK, 2=Pārbaudīt LIC datus, 3=Pārbaudīt grāmatvedības datus un LIC, 4=norakstīt',
PRIMARY KEY (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci AUTO_INCREMENT=64 ;
CREATE TABLE IF NOT EXISTS `device_type` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(254) COLLATE utf8_latvian_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci AUTO_INCREMENT=14 ;
INSERT INTO `device_type` (`id`, `name`) VALUES
(1, 'Dators'),
(2, 'Monitors'),
(3, 'UPS'),
(4, 'Mobilais telefons'),
(5, 'Klaviatūra'),
(6, 'Pele'),
(7, 'Tīkla switch'),
(8, 'Printeris'),
(9, 'Portatīvais dators'),
(10, 'Printeris All-in-one'),
(11, 'Serveris'),
(12, 'cits'),
(13, 'Plaukstdators');
CREATE TABLE IF NOT EXISTS `invoices` (
`invoice_id` smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'id',
`date` varchar(10) COLLATE utf8_latvian_ci DEFAULT NULL COMMENT 'datums',
`set` varchar(7) COLLATE utf8_latvian_ci DEFAULT NULL COMMENT 'sērija',
`number` varchar(12) COLLATE utf8_latvian_ci DEFAULT NULL COMMENT 'numurs',
`supplier_id` smallint(6) DEFAULT NULL COMMENT 'piegādātāja id no tabulas party',
`customer_id` smallint(6) DEFAULT NULL COMMENT 'saņēmēja id no tabulas party',
PRIMARY KEY (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci AUTO_INCREMENT=144 ;
CREATE TABLE IF NOT EXISTS `licenses` (
`software_id` smallint(6) NOT NULL,
`invoice_id` smallint(6) NOT NULL,
`license_id` smallint(6) NOT NULL AUTO_INCREMENT,
`device_id` smallint(6) DEFAULT NULL,
`konts` smallint(6) NOT NULL,
`inv_id` smallint(6) NOT NULL,
`description` text COLLATE utf8_latvian_ci NOT NULL,
`status` tinyint(4) NOT NULL,
PRIMARY KEY (`license_id`),
KEY `invoice_id` (`invoice_id`),
KEY `device_id` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci AUTO_INCREMENT=522 ;
--
-- Dumping data for table `licenses`
--
-- --------------------------------------------------------
--
-- Table structure for table `license_type`
--
CREATE TABLE IF NOT EXISTS `license_type` (
`license_type_id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(254) COLLATE utf8_latvian_ci NOT NULL,
PRIMARY KEY (`license_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci AUTO_INCREMENT=9 ;
--
-- Dumping data for table `license_type`
--
INSERT INTO `license_type` (`license_type_id`, `name`) VALUES
(1, 'OEM'),
(2, 'ab. pagar.'),
(3, 'UPG'),
(4, 'Retail'),
(5, 'OLP'),
(6, 'License'),
(7, 'CAL'),
(8, 'Upgrade');
-- --------------------------------------------------------
--
-- Table structure for table `list_bilances_konti`
--
CREATE TABLE IF NOT EXISTS `list_bilances_konti` (
`id` smallint(6) NOT NULL,
`name` varchar(254) COLLATE utf8_latvian_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci;
--
-- Dumping data for table `list_bilances_konti`
--
INSERT INTO `list_bilances_konti` (`id`, `name`) VALUES
(1120, 'Koncesijas, patenti, licences, datorprogrammas'),
(1230, 'Datori un skaitļošanas iekārtas'),
(7275, 'Mazvērtīgais inventārs');
-- --------------------------------------------------------
--
-- Table structure for table `party`
--
CREATE TABLE IF NOT EXISTS `party` (
`party_id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(254) COLLATE utf8_latvian_ci NOT NULL,
`type` smallint(6) NOT NULL,
PRIMARY KEY (`party_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci AUTO_INCREMENT=55 ;
--
-- Dumping data for table `party`
--
INSERT INTO `party` (`party_id`, `name`, `type`) VALUES
(1, 'UniCredit Leasing SIA', 2),
(2, 'Adminika Tehnoloģiju birojs SIA', 2),
(3, 'ES data SIA', 2),
(4, 'TL-1 SIA', 2),
(35, 'Sadzīves pakalpojumi', 1),
(6, 'DAIS SIA', 2),
(7, 'Partner Soft SIA', 2),
(8, 'Elko-Vecrīga', 2),
(9, 'Karšu izdevniecība Jāņa sēta', 2),
(11, 'NRG, SIA', 2),
(12, 'Minapa', 2),
(13, 'Partner Soft', 2),
(14, 'DT Mobile', 2),
(15, 'ODC, SIA', 2),
(32, 'EET Rīga', 2),
(33, 'EGATE', 2),
(36, 'Gaiļezers+', 1),
(37, 'KMK projekti', 1),
(38, 'DTC Grupa', 2),
(39, 'SIA "77"', 2),
(40, 'Datorlīzings', 2),
(41, 'Gaiļezers+', 2),
(42, 'CRC', 2),
(43, 'Euroscreen', 2),
(44, 'DOMA', 2),
(45, 'BRABUS', 2),
(46, 'ABCPC', 2),
(47, 'SPX', 2),
(48, 'SONEX RIGA', 2),
(49, 'BT CENTRS', 2),
(50, 'Multisistēma Rīga SIA', 2),
(51, 'Prikulis data service', 2),
(52, 'Mobilukss, SIA', 2),
(53, 'MikroKods, SIA', 2),
(54, 'Capital, A/S', 2);
-- --------------------------------------------------------
--
-- Table structure for table `party_type`
--
CREATE TABLE IF NOT EXISTS `party_type` (
`party_type_id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(254) COLLATE utf8_latvian_ci NOT NULL,
PRIMARY KEY (`party_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci AUTO_INCREMENT=3 ;
--
-- Dumping data for table `party_type`
--
INSERT INTO `party_type` (`party_type_id`, `name`) VALUES
(2, 'Supplier'),
(1, 'End User');
-- --------------------------------------------------------
--
-- Table structure for table `software`
--
CREATE TABLE IF NOT EXISTS `software` (
`software_id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(254) COLLATE utf8_latvian_ci NOT NULL,
`license_type` varchar(254) COLLATE utf8_latvian_ci NOT NULL COMMENT 'oem, olp, etc',
PRIMARY KEY (`software_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci AUTO_INCREMENT=96 ;
--
-- Dumping data for table `software`
--
INSERT INTO `software` (`software_id`, `name`, `license_type`) VALUES
(1, 'Microsoft Windows XP Professional', '1'),
(2, 'Microsoft Office XP Small Business Edition', '1'),
(3, 'Microsoft Office 2003 Basic Edition', '1'),
(4, 'Microsoft Office 2003 Small Business Edition', '1'),
(5, 'Norton Antivirus 2004', '1'),
(6, 'Tildes Briojs 2002', '1'),
(7, 'Antivirus eTrust v 7.0', '1'),
(8, 'Retrospect 6.0 Express', '1'),
(9, 'Tildes Jumis 2004 jauninājums 1gads ', '2'),
(10, 'Tildes Jumis 2004 papildus darba vietas jauninājums 1gads ', '2'),
(11, 'Norton Antivirus 2005 6pack', '3'),
(12, 'Microsoft Windows Vista Business', '1'),
(13, 'Microsoft Office 2007 English SBS', '5'),
(14, 'Winrar', '6'),
(15, 'Tildes Winlogs 2005', '4'),
(16, 'Tildes Birojs 2005', '1'),
(17, 'Microsoft Office 2007 Standart', '5'),
(18, 'DAIS datorprogramma WIN_FNS', '6'),
(19, 'DAIS datorprogramma ALGAS', '6'),
(20, 'Nomas un noliktavas uzskaite 1C vidē', '6'),
(21, 'Microsoft Windows SBS CAL 2003 English', '1'),
(22, 'Tildes Winlogs 2005', '1'),
(23, 'Symantec Antivirus Corporate Edition Workstations', '6'),
(24, 'Symantec Brightmail Antispam & Antivirus 6.0', '6'),
(25, 'Microsoft Windows XP Professional Retail', '4'),
(26, 'Microsoft Project 2007 English Standard', '5'),
(27, 'Norton Antivirus 2007', '4'),
(28, 'Symantec Brightmail Antispam & Antivirus 6.0', '2'),
(29, 'Symantec Antivirus Corporate Edition Workstations', '2'),
(30, 'Microsoft Windows SBS Standart 2003', '1'),
(31, 'Microsoft Windows XP HOME', '1'),
(32, 'Microsoft Windows Server Standart 2008 32Bit\\x64 w/o HypV', '1'),
(33, 'Symantec Multi-Tier Protection 11.0', '8'),
(34, 'Symantec Multi-Tier Protection 11.0', '6'),
(35, 'Microsoft Office 2007 Small Business', '5'),
(36, 'Tildes Winlogs 2008', '1'),
(37, 'Tildes Birojs 2008 ar abonementu uz 1 gadu', '4'),
(38, 'Microsoft Office 2007 Small Business', '1'),
(39, 'Kaspersky AntiVirus 7.0 1 year', '6'),
(40, 'Apvārsnis servera licence', '6'),
(41, 'Jāņa Sēta - karšu sistēma JS Baltija GPS versija 50 objekti', '6'),
(42, 'Tildes Jumis 2000 CD', '6'),
(43, 'Tildes Birojs 2005 CD', '4'),
(44, 'Microsoft Windows XP Professional Genuine KIT', '4'),
(45, 'Autocad 2009 Lite', '6'),
(46, 'Autocad 2010 LT New SLM ', '4'),
(47, 'ESET NOD32 Antivirus Business Edition 25-49', '6'),
(48, 'Microsoft Visio Standart 2007 OLP', '5'),
(63, 'SSL sertifikāts (1 gads)', '6'),
(62, 'AutoCAD LT 2009 New 5 seats SLM', '4'),
(61, 'ESET NOD32 Antivirus for 3 workstation 1 year ', '6'),
(60, 'ESET NOD32 Antivirus for 2 workstation 1 year', '6'),
(53, 'Microsoft Windows Vista Home Basic', '1'),
(52, 'Norton Internet Security 2008 BA CD RET', '4'),
(51, 'Tildes Birojs 2008 OEM', '1'),
(50, 'Kaspersky Antivirus 2009 RENEWAL 1Y', '2'),
(49, 'Kaspersky Antivirus 2009 1user 1Y', '4'),
(64, 'Domēna vārds FQDN', '6'),
(65, 'Windows 7 Pro OEM Eng 32-bit', '1'),
(66, 'Windows 7 Home Premium OEM 32-bit', '1'),
(76, 'MS Office Home&Business 2010', '1'),
(77, 'Microsoft Project 2010', '5'),
(78, 'Autocad 2011', '4'),
(81, 'Microsoft Office 2007 Basic OEM', '1'),
(82, 'Panda AntiVirus 2008 OEM 1 year', '1'),
(83, 'Tildes Jumis 2006 ar abonamentu uz 1 gadu', '4'),
(84, 'ESET NOD32 Retail', '4'),
(85, 'ESET NOD32 Retail 2 year', '4'),
(86, 'Microsoft Office 2003 Pro OEM', '1'),
(87, 'Abby FineReader Pro 8.0', '4'),
(88, 'Autocad LT 2006', '4'),
(89, 'Bentley Power Draft prog ar lic.', '4'),
(90, 'Windows 7 Pro 64bit Eng OEM', '1'),
(91, 'Bentley PowerDraft XM*', '4'),
(92, 'JS Latvija Standart', '4'),
(93, 'Microsoft Windows Server 2003 Standart', '1'),
(94, 'Microsoft Windows Server 2003 Standart CAL 5CLT', '1'),
(95, 'PANDA ANTIVIRUS 2012 RNW 3PC 1Y', '2');
CREATE TABLE IF NOT EXISTS `users` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_latvian_ci NOT NULL,
`surname` varchar(50) COLLATE utf8_latvian_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci AUTO_INCREMENT=86 ;
ALTER TABLE `licenses`
ADD CONSTRAINT `licenses_ibfk_3` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
ADD CONSTRAINT `licenses_ibfk_4` FOREIGN KEY (`device_id`) REFERENCES `devices` (`device_id`) ON DELETE SET NULL;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;