PageRenderTime 29ms CodeModel.GetById 21ms RepoModel.GetById 0ms app.codeStats 1ms

/install/sql/yabs-1.0-derby.sql

http://mp-rechnungs-und-kundenverwaltung.googlecode.com/
SQL | 1668 lines | 1383 code | 285 blank | 0 comment | 0 complexity | 558ee7e4247e1702e9a0e2207e049509 MD5 | raw file
Possible License(s): LGPL-3.0, Apache-2.0, GPL-3.0, GPL-2.0, AGPL-3.0, JSON, BSD-3-Clause
  1. CREATE TABLE ACCOUNTS (
  2. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  3. INTACCOUNTCLASS SMALLINT DEFAULT 0,
  4. CNAME VARCHAR(250) NOT NULL,
  5. DESCRIPTION VARCHAR(250) NOT NULL,
  6. TAXVALUE DOUBLE NOT NULL DEFAULT 0,
  7. DATEADDED DATE NOT NULL,
  8. INTADDEDBY BIGINT(20) DEFAULT 0,
  9. INTPARENTACCOUNT BIGINT(20) DEFAULT 0,
  10. GROUPSIDS BIGINT(20) DEFAULT 1,
  11. INVISIBLE SMALLINT DEFAULT 0,
  12. INTACCOUNTTYPE SMALLINT NOT NULL,
  13. INTPROFITFID SMALLINT NOT NULL,
  14. INTTAXFID SMALLINT NOT NULL,
  15. INTTAXUID SMALLINT NOT NULL,
  16. FRAME VARCHAR(25) NOT NULL,
  17. HIERARCHYPATH VARCHAR(250) DEFAULT NULL
  18. );
  19. CREATE TABLE ADDRESSES (
  20. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  21. GROUPSIDS BIGINT(20) DEFAULT 1,
  22. CONTACTSIDS BIGINT(20),
  23. TITLE VARCHAR(250) DEFAULT NULL,
  24. TAXNUMBER VARCHAR(250),
  25. PRENAME VARCHAR(250) DEFAULT NULL,
  26. CNAME VARCHAR(250) DEFAULT NULL,
  27. STREET VARCHAR(250) DEFAULT NULL,
  28. ZIP VARCHAR(50) DEFAULT NULL,
  29. CITY VARCHAR(300) DEFAULT NULL,
  30. COMPANY VARCHAR(250) DEFAULT NULL,
  31. DEPARTMENT VARCHAR(250) DEFAULT NULL,
  32. COUNTRY VARCHAR(50) DEFAULT NULL,
  33. ISMALE SMALLINT DEFAULT 0,
  34. INTADDEDBY BIGINT(20) DEFAULT 0,
  35. DATEADDED DATE DEFAULT NULL,
  36. INTTYPE SMALLINT DEFAULT 0,
  37. RESERVE1 VARCHAR(500) DEFAULT NULL,
  38. RESERVE2 VARCHAR(500) DEFAULT NULL
  39. );
  40. CREATE TABLE COMPS (
  41. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  42. GROUPSIDS BIGINT(20) DEFAULT 1,
  43. CNAME VARCHAR(250) DEFAULT NULL,
  44. STATE VARCHAR(250) DEFAULT NULL,
  45. PHONEPREFIX VARCHAR(250) DEFAULT NULL,
  46. BUSINESS VARCHAR(250) DEFAULT NULL,
  47. TAXADVISOR VARCHAR(250) DEFAULT NULL,
  48. CITY VARCHAR(250) DEFAULT NULL,
  49. TAXADVJOB VARCHAR(250) DEFAULT NULL,
  50. STREET VARCHAR(250) DEFAULT NULL,
  51. STB VARCHAR(250) DEFAULT NULL,
  52. EMAIL VARCHAR(250) DEFAULT NULL,
  53. NAME VARCHAR(250) DEFAULT NULL,
  54. ZIPCODE VARCHAR(250) DEFAULT NULL,
  55. PHONE VARCHAR(250) DEFAULT NULL,
  56. FIRSTNAME VARCHAR(250) DEFAULT NULL,
  57. TAXAUTHORITY VARCHAR(250) DEFAULT NULL,
  58. TAXNUMBER VARCHAR(250) DEFAULT NULL,
  59. TAXADVMANDANT VARCHAR(250) DEFAULT NULL,
  60. DATEADDED DATE NOT NULL,
  61. ISMALE SMALLINT DEFAULT 0,
  62. ISENABLED SMALLINT DEFAULT 1,
  63. INTADDEDBY BIGINT(20) DEFAULT 0,
  64. INVISIBLE SMALLINT DEFAULT 0,
  65. RESERVE1 VARCHAR(500) DEFAULT NULL,
  66. RESERVE2 VARCHAR(500) DEFAULT NULL
  67. );
  68. CREATE TABLE CONTACTS (
  69. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  70. CNUMBER VARCHAR(250) DEFAULT NULL,
  71. TAXNUMBER VARCHAR(250),
  72. TITLE VARCHAR(250) DEFAULT NULL,
  73. GROUPSIDS BIGINT(20) DEFAULT 1,
  74. COUNTRY VARCHAR(50) DEFAULT NULL,
  75. PRENAME VARCHAR(250) DEFAULT NULL,
  76. CNAME VARCHAR(250) DEFAULT NULL,
  77. STREET VARCHAR(250) DEFAULT NULL,
  78. ZIP VARCHAR(50) DEFAULT NULL,
  79. CITY VARCHAR(300) DEFAULT NULL,
  80. MAINPHONE VARCHAR(250) DEFAULT NULL,
  81. FAX VARCHAR(250) DEFAULT NULL,
  82. MOBILEPHONE VARCHAR(250) DEFAULT NULL,
  83. WORKPHONE VARCHAR(250) DEFAULT NULL,
  84. MAILADDRESS VARCHAR(350) DEFAULT NULL,
  85. COMPANY VARCHAR(250) DEFAULT NULL,
  86. DEPARTMENT VARCHAR(250) DEFAULT NULL,
  87. WEBSITE VARCHAR(350) DEFAULT NULL,
  88. NOTES VARCHAR(10000),
  89. DATEADDED DATE NOT NULL,
  90. ISACTIVE SMALLINT DEFAULT 0,
  91. ISCUSTOMER SMALLINT DEFAULT 0,
  92. ISMANUFACTURER SMALLINT DEFAULT 0,
  93. ISSUPPLIER SMALLINT DEFAULT 0,
  94. ISCOMPANY SMALLINT DEFAULT 0,
  95. ISMALE SMALLINT DEFAULT 0,
  96. ISENABLED SMALLINT DEFAULT 1,
  97. INTADDEDBY BIGINT(20) DEFAULT 0,
  98. INVISIBLE SMALLINT DEFAULT 0,
  99. RESERVE1 VARCHAR(500) DEFAULT NULL,
  100. RESERVE2 VARCHAR(500) DEFAULT NULL
  101. );
  102. CREATE TABLE COUNTRIES (
  103. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  104. CNAME VARCHAR(250) NOT NULL,
  105. ISO SMALLINT NOT NULL,
  106. GROUPSIDS BIGINT(20) DEFAULT 1,
  107. RESERVE1 VARCHAR(500) DEFAULT NULL,
  108. RESERVE2 VARCHAR(500) DEFAULT NULL
  109. );
  110. CREATE TABLE EXPENSES (
  111. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  112. GROUPSIDS BIGINT(20) DEFAULT 1,
  113. CNUMBER VARCHAR(250) DEFAULT NULL,
  114. CNAME VARCHAR(250) DEFAULT NULL,
  115. ACCOUNTSIDS BIGINT(20),
  116. NETVALUE DOUBLE DEFAULT 0,
  117. TAXPERCENTVALUE DOUBLE DEFAULT 0,
  118. BRUTVALUE DOUBLE DEFAULT 0,
  119. DESCRIPTION VARCHAR(5000) DEFAULT NULL,
  120. DATEADDED DATE NOT NULL,
  121. INTADDEDBY BIGINT(20) DEFAULT 0,
  122. INVISIBLE SMALLINT DEFAULT 0,
  123. RESERVE1 VARCHAR(500) DEFAULT NULL,
  124. RESERVE2 VARCHAR(500) DEFAULT NULL
  125. );
  126. CREATE TABLE FAVOURITES (
  127. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  128. CNAME VARCHAR(250) NOT NULL,
  129. USERSIDS BIGINT(20),
  130. GROUPSIDS BIGINT(20) DEFAULT 1,
  131. ITEMSIDS BIGINT(20) NOT NULL,
  132. DATEADDED DATE NOT NULL,
  133. INTADDEDBY BIGINT(20) DEFAULT 0,
  134. INVISIBLE SMALLINT DEFAULT 0,
  135. RESERVE1 VARCHAR(500) DEFAULT NULL,
  136. RESERVE2 VARCHAR(500) DEFAULT NULL
  137. );
  138. CREATE TABLE FILES (
  139. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  140. CNAME VARCHAR(25) NOT NULL,
  141. GROUPSIDS BIGINT(20) DEFAULT 1,
  142. DATEADDED DATE NOT NULL,
  143. DATA BLOB(5242880) NOT NULL,
  144. FILESIZE BIGINT(20) NOT NULL,
  145. INTADDEDBY BIGINT(20) DEFAULT 0,
  146. INVISIBLE SMALLINT DEFAULT 0
  147. );
  148. CREATE TABLE FILESTOCONTACTS (
  149. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  150. CNAME VARCHAR(250) NOT NULL,
  151. DESCRIPTION VARCHAR(550) DEFAULT NULL,
  152. CONTACTSIDS BIGINT(20) NOT NULL,
  153. FILENAME VARCHAR(25) NOT NULL,
  154. INTSIZE BIGINT(20) DEFAULT 0,
  155. MIMETYPE VARCHAR(25) DEFAULT NULL,
  156. INTADDEDBY BIGINT(20) DEFAULT 0,
  157. DATEADDED DATE NOT NULL,
  158. GROUPSIDS BIGINT(20) DEFAULT 1
  159. );
  160. CREATE TABLE FILESTOITEMS (
  161. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  162. CNAME VARCHAR(250) NOT NULL,
  163. DESCRIPTION VARCHAR(550) DEFAULT NULL,
  164. ITEMSIDS BIGINT(20) NOT NULL,
  165. FILENAME VARCHAR(25) NOT NULL,
  166. INTSIZE BIGINT(20) DEFAULT 0,
  167. MIMETYPE VARCHAR(25) DEFAULT NULL,
  168. INTADDEDBY BIGINT(20) DEFAULT 0,
  169. DATEADDED DATE NOT NULL,
  170. GROUPSIDS BIGINT(20) DEFAULT 1
  171. );
  172. CREATE TABLE FILESTOPRODUCTS (
  173. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  174. CNAME VARCHAR(250) NOT NULL,
  175. DESCRIPTION VARCHAR(550) DEFAULT NULL,
  176. PRODUCTSIDS BIGINT(20) NOT NULL,
  177. FILENAME VARCHAR(25) NOT NULL,
  178. INTSIZE BIGINT(20) DEFAULT 0,
  179. MIMETYPE VARCHAR(25) DEFAULT NULL,
  180. INTADDEDBY BIGINT(20) DEFAULT 0,
  181. DATEADDED DATE NOT NULL,
  182. GROUPSIDS BIGINT(20) DEFAULT 1
  183. );
  184. CREATE TABLE FORMATSTOUSERS (
  185. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  186. CNAME VARCHAR(250) NOT NULL,
  187. USERSIDS BIGINT(20),
  188. INTTYPE SMALLINT DEFAULT 0
  189. );
  190. CREATE TABLE GLOBALSETTINGS (
  191. IDS BIGINT(20) DEFAULT 1,
  192. CNAME VARCHAR(250) NOT NULL,
  193. GROUPSIDS BIGINT(20) DEFAULT 1,
  194. VALUE VARCHAR(250) NOT NULL
  195. );
  196. CREATE TABLE GROUPS (
  197. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  198. CNAME VARCHAR(250) NOT NULL,
  199. DESCRIPTION VARCHAR(750) DEFAULT NULL,
  200. DEFAULTS VARCHAR(250) DEFAULT NULL,
  201. GROUPSIDS BIGINT(20) DEFAULT 0,
  202. DATEADDED DATE NOT NULL,
  203. RESERVE1 VARCHAR(500) DEFAULT NULL,
  204. INTADDEDBY BIGINT(20) DEFAULT 0,
  205. HIERARCHYPATH VARCHAR(500) DEFAULT NULL,
  206. RESERVE2 VARCHAR(500) DEFAULT NULL
  207. );
  208. CREATE TABLE HISTORY (
  209. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  210. CNAME VARCHAR(250) NOT NULL,
  211. USERNAME VARCHAR(250) NOT NULL,
  212. DBIDENTITY VARCHAR(25) NOT NULL,
  213. INTITEM SMALLINT NOT NULL,
  214. GROUPSIDS BIGINT(20) DEFAULT 0,
  215. DATEADDED DATE NOT NULL,
  216. INTADDEDBY BIGINT(20) DEFAULT 0,
  217. RESERVE1 VARCHAR(500) DEFAULT NULL,
  218. RESERVE2 VARCHAR(500) DEFAULT NULL
  219. );
  220. CREATE TABLE ITEMS (
  221. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  222. CNAME VARCHAR(250) NOT NULL,
  223. CNUMBER VARCHAR(250) NOT NULL,
  224. DESCRIPTION VARCHAR(2500) DEFAULT NULL,
  225. GROUPSIDS BIGINT(20) DEFAULT 1,
  226. ACCOUNTSIDS BIGINT(20) DEFAULT 1,
  227. CONTACTSIDS BIGINT(20),
  228. NETVALUE DOUBLE DEFAULT 0,
  229. TAXVALUE DOUBLE DEFAULT 0,
  230. DISCOUNTVALUE DOUBLE DEFAULT 0,
  231. SHIPPINGVALUE DOUBLE DEFAULT 0,
  232. DATETODO DATE DEFAULT NULL,
  233. DATEEND DATE DEFAULT NULL,
  234. INTREMINDERS INTEGER DEFAULT 0,
  235. INTTYPE SMALLINT DEFAULT 0,
  236. DATEADDED DATE NOT NULL,
  237. INTADDEDBY BIGINT(20) DEFAULT 0,
  238. INVISIBLE SMALLINT DEFAULT 0,
  239. INTSTATUS SMALLINT DEFAULT 0,
  240. HIERARCHYPATH VARCHAR(500) DEFAULT NULL,
  241. RESERVE1 VARCHAR(500) DEFAULT NULL,
  242. RESERVE2 VARCHAR(500) DEFAULT NULL
  243. );
  244. CREATE TABLE ITEMSTOACCOUNTS (
  245. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  246. ITEMSIDS BIGINT(20) NOT NULL,
  247. ACCOUNTSIDS BIGINT(20),
  248. RESERVE1 VARCHAR(500) DEFAULT NULL,
  249. RESERVE2 VARCHAR(500) DEFAULT NULL
  250. );
  251. CREATE TABLE LANGUAGES (
  252. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  253. CNAME VARCHAR(250) NOT NULL,
  254. GROUPSIDS BIGINT(20) DEFAULT 1,
  255. LONGNAME VARCHAR(250) NOT NULL,
  256. FILENAME VARCHAR(25) NOT NULL,
  257. DATEADDED DATE NOT NULL,
  258. INTADDEDBY BIGINT(20) DEFAULT 0,
  259. INVISIBLE SMALLINT DEFAULT 0,
  260. RESERVE1 VARCHAR(500) DEFAULT NULL,
  261. RESERVE2 VARCHAR(500) DEFAULT NULL
  262. );
  263. CREATE TABLE MAILS (
  264. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  265. CNAME VARCHAR(1000) NOT NULL,
  266. DATEADDED DATE NOT NULL,
  267. INTADDEDBY BIGINT(20) DEFAULT 0,
  268. GROUPSIDS BIGINT(20) DEFAULT 1,
  269. INVISIBLE SMALLINT DEFAULT 0,
  270. USERSIDS BIGINT(20),
  271. DESCRIPTION VARCHAR(5000) NOT NULL
  272. );
  273. CREATE TABLE PLUGINS (
  274. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  275. CNAME VARCHAR(250) NOT NULL,
  276. DESCRIPTION VARCHAR(550) DEFAULT NULL,
  277. FILENAME VARCHAR(25) NOT NULL,
  278. INTADDEDBY BIGINT(20) DEFAULT 0,
  279. DATEADDED DATE NOT NULL,
  280. GROUPSIDS BIGINT(20) DEFAULT 1
  281. );
  282. CREATE TABLE PLUGINSTOUSERS (
  283. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  284. CNAME VARCHAR(250) NOT NULL,
  285. USERSIDS BIGINT(20) NOT NULL,
  286. PLUGINSIDS BIGINT(20) NOT NULL,
  287. INTADDEDBY BIGINT(20) DEFAULT 0,
  288. DATEADDED DATE NOT NULL,
  289. GROUPSIDS BIGINT(20) DEFAULT 1
  290. );
  291. CREATE TABLE PRODUCTGROUPS (
  292. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  293. CNAME VARCHAR(250) NOT NULL,
  294. DESCRIPTION VARCHAR(750) DEFAULT NULL,
  295. DEFAULTS VARCHAR(250) DEFAULT NULL,
  296. GROUPSIDS BIGINT(20) DEFAULT 0,
  297. PRODUCTGROUPSIDS BIGINT(20) DEFAULT 0,
  298. DATEADDED DATE NOT NULL,
  299. HIERARCHYPATH VARCHAR(500) DEFAULT NULL,
  300. RESERVE1 VARCHAR(500) DEFAULT NULL,
  301. INTADDEDBY BIGINT(20) DEFAULT 0,
  302. RESERVE2 VARCHAR(500) DEFAULT NULL
  303. );
  304. CREATE TABLE PRODUCTLISTITEMS (
  305. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  306. CNAME VARCHAR(2500) DEFAULT NULL,
  307. LINKURL VARCHAR(250) DEFAULT NULL,
  308. GROUPSIDS BIGINT(20) DEFAULT 1,
  309. PRODUCTLISTSIDS BIGINT(20),
  310. ORIGINALPRODUCTSIDS BIGINT(20),
  311. COUNTVALUE DOUBLE NOT NULL DEFAULT 0,
  312. QUANTITYVALUE DOUBLE NOT NULL DEFAULT 0,
  313. MEASURE VARCHAR(250) NOT NULL,
  314. DESCRIPTION VARCHAR(1000) DEFAULT NULL,
  315. INTERNALVALUE DOUBLE DEFAULT 0,
  316. TOTALNETVALUE DOUBLE DEFAULT 0,
  317. TOTALBRUTVALUE DOUBLE DEFAULT 0,
  318. EXTERNALVALUE DOUBLE DEFAULT 0,
  319. TAXPERCENTVALUE DOUBLE NOT NULL DEFAULT 0,
  320. DATEADDED DATE NOT NULL,
  321. INTADDEDBY BIGINT(20) DEFAULT 0,
  322. INVISIBLE SMALLINT DEFAULT 0,
  323. RESERVE1 VARCHAR(500) DEFAULT NULL,
  324. RESERVE2 VARCHAR(500) DEFAULT NULL
  325. );
  326. CREATE TABLE PRODUCTLISTS (
  327. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  328. CNAME VARCHAR(2500) DEFAULT NULL,
  329. GROUPSIDS BIGINT(20) DEFAULT 1,
  330. DESCRIPTION VARCHAR(1000) DEFAULT NULL,
  331. DATEADDED DATE NOT NULL,
  332. INTADDEDBY BIGINT(20) DEFAULT 0,
  333. INVISIBLE SMALLINT DEFAULT 0,
  334. RESERVE1 VARCHAR(500) DEFAULT NULL,
  335. RESERVE2 VARCHAR(500) DEFAULT NULL
  336. );
  337. CREATE TABLE PRODUCTS (
  338. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  339. CNAME VARCHAR(500) NOT NULL,
  340. CNUMBER VARCHAR(250) NOT NULL,
  341. DESCRIPTION VARCHAR(5000),
  342. EXTERNALNETVALUE DOUBLE DEFAULT 0,
  343. INTERNALNETVALUE DOUBLE DEFAULT 0,
  344. MEASURE VARCHAR(250) NOT NULL,
  345. TAXIDS BIGINT(20),
  346. MANUFACTURERSIDS BIGINT(20) DEFAULT 0,
  347. SUPPLIERSIDS BIGINT(20) DEFAULT 0,
  348. GROUPSIDS BIGINT(20) DEFAULT 1,
  349. PRODUCTGROUPSIDS BIGINT(20) DEFAULT 1,
  350. URL VARCHAR(250) DEFAULT NULL,
  351. EAN VARCHAR(25) DEFAULT NULL,
  352. REFERENCE VARCHAR(50) DEFAULT NULL,
  353. DATEADDED DATE NOT NULL,
  354. INTADDEDBY BIGINT(20) DEFAULT 0,
  355. INVISIBLE SMALLINT DEFAULT 0,
  356. INTTYPE SMALLINT NOT NULL,
  357. DEFAULTIMAGE VARCHAR(30) DEFAULT NULL,
  358. RESERVE1 VARCHAR(500) DEFAULT NULL,
  359. RESERVE2 VARCHAR(500) DEFAULT NULL
  360. );
  361. CREATE TABLE REMINDERS (
  362. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  363. CNAME VARCHAR(250) NOT NULL,
  364. DESCRIPTION VARCHAR(550) DEFAULT NULL,
  365. STAGESIDS BIGINT(20) NOT NULL,
  366. ITEMSIDS BIGINT(20) NOT NULL,
  367. EXTRAVALUE DOUBLE DEFAULT 0,
  368. INTADDEDBY BIGINT(20) DEFAULT 0,
  369. DATEADDED DATE NOT NULL,
  370. GROUPSIDS BIGINT(20) DEFAULT 1
  371. );
  372. CREATE TABLE REVENUES (
  373. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  374. GROUPSIDS BIGINT(20) DEFAULT 1,
  375. CNUMBER VARCHAR(250) DEFAULT NULL,
  376. CNAME VARCHAR(250) DEFAULT NULL,
  377. ACCOUNTSIDS BIGINT(20),
  378. NETVALUE DOUBLE DEFAULT 0,
  379. TAXPERCENTVALUE DOUBLE DEFAULT 0,
  380. BRUTVALUE DOUBLE DEFAULT 0,
  381. DESCRIPTION VARCHAR(5000) DEFAULT NULL,
  382. DATEADDED DATE NOT NULL,
  383. INTADDEDBY BIGINT(20) DEFAULT 0,
  384. INVISIBLE SMALLINT DEFAULT 0,
  385. RESERVE1 VARCHAR(500) DEFAULT NULL,
  386. RESERVE2 VARCHAR(500) DEFAULT NULL
  387. );
  388. CREATE TABLE SCHEDULE (
  389. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  390. CNAME VARCHAR(250) NOT NULL,
  391. GROUPSIDS BIGINT(20) DEFAULT 1,
  392. USERSIDS BIGINT(20),
  393. ITEMSIDS BIGINT(20),
  394. STOPDATE DATE NOT NULL,
  395. STARTDATE DATE NOT NULL,
  396. NEXTDATE DATE NOT NULL,
  397. ISDONE SMALLINT DEFAULT 0,
  398. INTERVALMONTH SMALLINT NOT NULL,
  399. DATEADDED DATE NOT NULL,
  400. INTADDEDBY BIGINT(20) DEFAULT 0,
  401. INVISIBLE SMALLINT DEFAULT 0,
  402. RESERVE1 VARCHAR(500) DEFAULT NULL,
  403. RESERVE2 VARCHAR(500) DEFAULT NULL
  404. );
  405. CREATE TABLE SEARCHINDEX (
  406. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  407. DBIDENTITY VARCHAR(25) NOT NULL,
  408. GROUPSIDS BIGINT(20),
  409. ROWID BIGINT(20) NOT NULL,
  410. TEXT VARCHAR(5000) DEFAULT NULL
  411. );
  412. CREATE TABLE STAGES (
  413. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  414. CNAME VARCHAR(250) NOT NULL,
  415. DESCRIPTION VARCHAR(550) DEFAULT NULL,
  416. EXTRAVALUE DOUBLE DEFAULT 0,
  417. INTADDEDBY BIGINT(20) DEFAULT 0,
  418. DATEADDED DATE NOT NULL,
  419. GROUPSIDS BIGINT(20) DEFAULT 1
  420. );
  421. CREATE TABLE SUBITEMS (
  422. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  423. CNAME VARCHAR(5000) DEFAULT NULL,
  424. ITEMSIDS BIGINT(20),
  425. GROUPSIDS BIGINT(20) DEFAULT 1,
  426. ORIGINALPRODUCTSIDS BIGINT(20) DEFAULT NULL,
  427. COUNTVALUE DOUBLE NOT NULL DEFAULT 0,
  428. QUANTITYVALUE DOUBLE NOT NULL DEFAULT 0,
  429. MEASURE VARCHAR(250) NOT NULL,
  430. LINKURL VARCHAR(1000) DEFAULT NULL,
  431. DESCRIPTION VARCHAR(1000) DEFAULT NULL,
  432. INTERNALVALUE DOUBLE DEFAULT 0,
  433. TOTALNETVALUE DOUBLE DEFAULT 0,
  434. TOTALBRUTVALUE DOUBLE DEFAULT 0,
  435. EXTERNALVALUE DOUBLE DEFAULT 0,
  436. TAXPERCENTVALUE DOUBLE NOT NULL DEFAULT 0,
  437. DATEDELIVERY DATE DEFAULT NULL,
  438. DATEADDED DATE NOT NULL,
  439. INTADDEDBY BIGINT(20) DEFAULT 0,
  440. INVISIBLE SMALLINT DEFAULT 0,
  441. RESERVE1 VARCHAR(500) DEFAULT NULL,
  442. RESERVE2 VARCHAR(500) DEFAULT NULL
  443. );
  444. CREATE TABLE TABLELOCK (
  445. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  446. CNAME VARCHAR(250),
  447. ROWID BIGINT(20) NOT NULL,
  448. USERSIDS BIGINT(20),
  449. RESERVE1 VARCHAR(500) DEFAULT NULL,
  450. RESERVE2 VARCHAR(500) DEFAULT NULL
  451. );
  452. CREATE TABLE TAX (
  453. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  454. CNAME VARCHAR(250),
  455. TAXVALUE DOUBLE DEFAULT 0,
  456. IDENTIFIER VARCHAR(250) DEFAULT NULL,
  457. GROUPSIDS BIGINT(20) DEFAULT 1,
  458. COUNTRY VARCHAR(50) DEFAULT NULL,
  459. DATEADDED DATE NOT NULL,
  460. INTADDEDBY BIGINT(20) DEFAULT 0,
  461. INVISIBLE SMALLINT DEFAULT 0,
  462. RESERVE1 VARCHAR(500) DEFAULT NULL,
  463. RESERVE2 VARCHAR(500) DEFAULT NULL
  464. );
  465. CREATE TABLE TEMPLATES (
  466. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  467. CNAME VARCHAR(250) NOT NULL,
  468. FORMAT VARCHAR(55) DEFAULT NULL,
  469. DESCRIPTION VARCHAR(550) DEFAULT NULL,
  470. INTSIZE BIGINT(20) DEFAULT 0,
  471. MIMETYPE VARCHAR(25) DEFAULT NULL,
  472. FILENAME VARCHAR(25) NOT NULL,
  473. INTADDEDBY BIGINT(20) DEFAULT 0,
  474. DATEADDED DATE NOT NULL,
  475. GROUPSIDS BIGINT(20) DEFAULT 1
  476. );
  477. CREATE TABLE TEMPLATESTOUSERS (
  478. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  479. CNAME VARCHAR(250) NOT NULL,
  480. USERSIDS BIGINT(20),
  481. TEMPLATESIDS BIGINT(20) NOT NULL,
  482. GROUPSIDS BIGINT(20) DEFAULT 1
  483. );
  484. CREATE TABLE TRASHBIN (
  485. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  486. CNAME VARCHAR(500),
  487. ROWID BIGINT(20) NOT NULL,
  488. DESCRIPTION VARCHAR(2500),
  489. DELETEME SMALLINT DEFAULT 1,
  490. RESERVE1 VARCHAR(500) DEFAULT NULL,
  491. RESERVE2 VARCHAR(500) DEFAULT NULL
  492. );
  493. CREATE TABLE USERPROPERTIES (
  494. IDS BIGINT(20) DEFAULT 1,
  495. CNAME VARCHAR(250) NOT NULL,
  496. VALUE VARCHAR(250) NOT NULL,
  497. USERSIDS BIGINT(20) NOT NULL,
  498. DATEADDED DATE NOT NULL,
  499. INTADDEDBY BIGINT(20) DEFAULT 0,
  500. GROUPSIDS BIGINT(20) DEFAULT 1,
  501. INVISIBLE SMALLINT DEFAULT 0
  502. );
  503. CREATE TABLE USERS (
  504. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  505. CNAME VARCHAR(250) NOT NULL,
  506. GROUPSIDS BIGINT(20) DEFAULT 1,
  507. INTDEFAULTACCOUNT BIGINT(20) DEFAULT 1,
  508. COMPSIDS BIGINT(20) DEFAULT 0,
  509. INTDEFAULTSTATUS BIGINT(20) DEFAULT 1,
  510. FULLNAME VARCHAR(250) NOT NULL,
  511. PASSWORD VARCHAR(250) NOT NULL,
  512. LAF VARCHAR(50) DEFAULT NULL,
  513. LOCALE VARCHAR(50) DEFAULT NULL,
  514. DEFCOUNTRY VARCHAR(50) DEFAULT NULL,
  515. MAIL VARCHAR(50) DEFAULT NULL,
  516. LANGUAGE VARCHAR(50) DEFAULT NULL,
  517. INTHIGHESTRIGHT SMALLINT DEFAULT 3,
  518. ISENABLED SMALLINT DEFAULT 1,
  519. ISRGROUPED SMALLINT DEFAULT 0,
  520. ISLOGGEDIN SMALLINT DEFAULT 0,
  521. DATELASTLOG DATE DEFAULT NULL,
  522. DATEADDED DATE NOT NULL,
  523. INTADDEDBY BIGINT(20) DEFAULT 0,
  524. INVISIBLE SMALLINT DEFAULT 0,
  525. RESERVE1 VARCHAR(500) DEFAULT NULL,
  526. RESERVE2 VARCHAR(500) DEFAULT NULL
  527. );
  528. CREATE TABLE WEBSHOPS (
  529. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  530. CNAME VARCHAR(250) NOT NULL,
  531. DESCRIPTION VARCHAR(750) DEFAULT NULL,
  532. GROUPSIDS BIGINT(20) DEFAULT 0,
  533. DATEADDED DATE NOT NULL,
  534. ISREQUESTCOMPRESSION SMALLINT DEFAULT 0,
  535. ISAUTHENTICATED SMALLINT DEFAULT 0,
  536. USERNAME VARCHAR(50) DEFAULT NULL,
  537. PASSWORD VARCHAR(50) DEFAULT NULL,
  538. RESERVE1 VARCHAR(500) DEFAULT NULL,
  539. INTADDEDBY BIGINT(20) DEFAULT 0,
  540. INTERVAL BIGINT(20) DEFAULT 0,
  541. URL VARCHAR(500) DEFAULT NULL,
  542. RESERVE2 VARCHAR(500) DEFAULT NULL
  543. );
  544. CREATE TABLE WSCONTACTSMAPPING (
  545. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  546. WEBSHOPSIDS BIGINT(20),
  547. CNAME VARCHAR(250) NOT NULL,
  548. GROUPSIDS BIGINT(20),
  549. CONTACTSIDS BIGINT(20),
  550. WSCONTACT VARCHAR(250) NOT NULL,
  551. DATEADDED DATE DEFAULT NULL,
  552. INTADDEDBY BIGINT(20) DEFAULT 0,
  553. INVISIBLE SMALLINT DEFAULT 0,
  554. RESERVE1 VARCHAR(500) DEFAULT NULL,
  555. RESERVE2 VARCHAR(500) DEFAULT NULL
  556. );
  557. CREATE TABLE WSITEMSMAPPING (
  558. IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  559. WEBSHOPSIDS BIGINT(20),
  560. CNAME VARCHAR(250) NOT NULL,
  561. GROUPSIDS BIGINT(20),
  562. ITEMSIDS BIGINT(20),
  563. WSITEM VARCHAR(250) NOT NULL,
  564. DATEADDED DATE DEFAULT NULL,
  565. INTADDEDBY BIGINT(20) DEFAULT 0,
  566. INVISIBLE SMALLINT DEFAULT 0,
  567. RESERVE1 VARCHAR(500) DEFAULT NULL,
  568. RESERVE2 VARCHAR(500) DEFAULT NULL
  569. );
  570. CREATE UNIQUE INDEX SQL091006084310810
  571. ON GROUPS(CNAME);
  572. CREATE UNIQUE INDEX SQL091006084310820
  573. ON GROUPS(IDS);
  574. CREATE UNIQUE INDEX SQL091006084311391
  575. ON PRODUCTGROUPS(CNAME, GROUPSIDS);
  576. CREATE UNIQUE INDEX SQL091006084311390
  577. ON PRODUCTGROUPS(IDS);
  578. CREATE UNIQUE INDEX SQL091006084311800
  579. ON HISTORY(IDS);
  580. CREATE UNIQUE INDEX SQL091006084312110
  581. ON COUNTRIES(ISO);
  582. CREATE UNIQUE INDEX SQL091006084312111
  583. ON COUNTRIES(IDS);
  584. CREATE UNIQUE INDEX SQL091006084312620
  585. ON GLOBALSETTINGS(CNAME, GROUPSIDS);
  586. CREATE UNIQUE INDEX SQL091006084312900
  587. ON TAX(IDS);
  588. CREATE INDEX SQL091006084312890
  589. ON TAX(GROUPSIDS);
  590. CREATE UNIQUE INDEX SQL091006084313452
  591. ON CONTACTS(CNUMBER, GROUPSIDS);
  592. CREATE UNIQUE INDEX SQL091006084313451
  593. ON CONTACTS(IDS);
  594. CREATE INDEX SQL091006084313450
  595. ON CONTACTS(GROUPSIDS);
  596. CREATE UNIQUE INDEX SQL091006084313971
  597. ON USERS(CNAME, GROUPSIDS);
  598. CREATE UNIQUE INDEX SQL091006084313970
  599. ON USERS(IDS);
  600. CREATE INDEX SQL091006084313960
  601. ON USERS(GROUPSIDS);
  602. CREATE UNIQUE INDEX SQL091006084314570
  603. ON FILES(CNAME);
  604. CREATE UNIQUE INDEX SQL091006084314572
  605. ON FILES(IDS);
  606. CREATE INDEX SQL091006084314571
  607. ON FILES(GROUPSIDS);
  608. CREATE UNIQUE INDEX SQL091006084315174
  609. ON LANGUAGES(CNAME, GROUPSIDS);
  610. CREATE UNIQUE INDEX SQL091006084315171
  611. ON LANGUAGES(LONGNAME);
  612. CREATE UNIQUE INDEX SQL091006084315173
  613. ON LANGUAGES(IDS);
  614. CREATE INDEX SQL091006084315170
  615. ON LANGUAGES(GROUPSIDS);
  616. CREATE INDEX SQL091006084315172
  617. ON LANGUAGES(FILENAME);
  618. CREATE UNIQUE INDEX SQL091006084315882
  619. ON FAVOURITES(IDS);
  620. CREATE INDEX SQL091006084315880
  621. ON FAVOURITES(USERSIDS);
  622. CREATE INDEX SQL091006084315881
  623. ON FAVOURITES(GROUPSIDS);
  624. CREATE UNIQUE INDEX SQL091006084316451
  625. ON ACCOUNTS(IDS);
  626. CREATE INDEX SQL091006084316450
  627. ON ACCOUNTS(GROUPSIDS);
  628. CREATE UNIQUE INDEX SQL091006084316862
  629. ON ITEMS(CNUMBER, INTTYPE);
  630. CREATE UNIQUE INDEX SQL091006084316861
  631. ON ITEMS(IDS);
  632. CREATE INDEX SQL091006084316850
  633. ON ITEMS(GROUPSIDS);
  634. CREATE INDEX SQL091006084316851
  635. ON ITEMS(ACCOUNTSIDS);
  636. CREATE INDEX SQL091006084316860
  637. ON ITEMS(CONTACTSIDS);
  638. CREATE UNIQUE INDEX SQL091006084317902
  639. ON SUBITEMS(IDS);
  640. CREATE INDEX SQL091006084317900
  641. ON SUBITEMS(ITEMSIDS);
  642. CREATE INDEX SQL091006084317901
  643. ON SUBITEMS(GROUPSIDS);
  644. CREATE UNIQUE INDEX SQL091006084318483
  645. ON SCHEDULE(IDS);
  646. CREATE INDEX SQL091006084318480
  647. ON SCHEDULE(GROUPSIDS);
  648. CREATE INDEX SQL091006084318481
  649. ON SCHEDULE(USERSIDS);
  650. CREATE INDEX SQL091006084318482
  651. ON SCHEDULE(ITEMSIDS);
  652. CREATE UNIQUE INDEX SQL091006084319621
  653. ON PRODUCTS(CNUMBER, GROUPSIDS);
  654. CREATE UNIQUE INDEX SQL091006084319620
  655. ON PRODUCTS(IDS);
  656. CREATE INDEX SQL091006084319610
  657. ON PRODUCTS(TAXIDS);
  658. CREATE INDEX SQL091006084319611
  659. ON PRODUCTS(GROUPSIDS);
  660. CREATE INDEX SQL091006084319612
  661. ON PRODUCTS(PRODUCTGROUPSIDS);
  662. CREATE UNIQUE INDEX SQL091006084320591
  663. ON PRODUCTLISTS(IDS);
  664. CREATE INDEX SQL091006084320590
  665. ON PRODUCTLISTS(GROUPSIDS);
  666. CREATE UNIQUE INDEX SQL091006084321141
  667. ON PRODUCTLISTITEMS(IDS);
  668. CREATE INDEX SQL091006084321130
  669. ON PRODUCTLISTITEMS(GROUPSIDS);
  670. CREATE INDEX SQL091006084321131
  671. ON PRODUCTLISTITEMS(PRODUCTLISTSIDS);
  672. CREATE INDEX SQL091006084321140
  673. ON PRODUCTLISTITEMS(ORIGINALPRODUCTSIDS);
  674. CREATE INDEX SQL091006084322190
  675. ON USERPROPERTIES(GROUPSIDS);
  676. CREATE UNIQUE INDEX SQL091006084322552
  677. ON MAILS(IDS);
  678. CREATE INDEX SQL091006084322550
  679. ON MAILS(GROUPSIDS);
  680. CREATE INDEX SQL091006084322551
  681. ON MAILS(USERSIDS);
  682. CREATE UNIQUE INDEX SQL091006084323381
  683. ON COMPS(IDS);
  684. CREATE INDEX SQL091006084323380
  685. ON COMPS(GROUPSIDS);
  686. CREATE UNIQUE INDEX SQL091006084324160
  687. ON EXPENSES(IDS);
  688. CREATE INDEX SQL091006084324140
  689. ON EXPENSES(GROUPSIDS);
  690. CREATE INDEX SQL091006084324141
  691. ON EXPENSES(ACCOUNTSIDS);
  692. CREATE UNIQUE INDEX SQL091006084324810
  693. ON REVENUES(IDS);
  694. CREATE INDEX SQL091006084324790
  695. ON REVENUES(GROUPSIDS);
  696. CREATE INDEX SQL091006084324800
  697. ON REVENUES(ACCOUNTSIDS);
  698. CREATE UNIQUE INDEX SQL091006084325551
  699. ON WEBSHOPS(CNAME, GROUPSIDS);
  700. CREATE UNIQUE INDEX SQL091006084325550
  701. ON WEBSHOPS(IDS);
  702. CREATE UNIQUE INDEX SQL091006084326054
  703. ON WSCONTACTSMAPPING(WSCONTACT, GROUPSIDS, WEBSHOPSIDS);
  704. CREATE UNIQUE INDEX SQL091006084326053
  705. ON WSCONTACTSMAPPING(IDS);
  706. CREATE INDEX SQL091006084326050
  707. ON WSCONTACTSMAPPING(WEBSHOPSIDS);
  708. CREATE INDEX SQL091006084326051
  709. ON WSCONTACTSMAPPING(GROUPSIDS);
  710. CREATE INDEX SQL091006084326052
  711. ON WSCONTACTSMAPPING(CONTACTSIDS);
  712. CREATE UNIQUE INDEX SQL091006084326881
  713. ON WSITEMSMAPPING(WSITEM, GROUPSIDS, WEBSHOPSIDS);
  714. CREATE UNIQUE INDEX SQL091006084326880
  715. ON WSITEMSMAPPING(IDS);
  716. CREATE INDEX SQL091006084326870
  717. ON WSITEMSMAPPING(WEBSHOPSIDS);
  718. CREATE INDEX SQL091006084326871
  719. ON WSITEMSMAPPING(GROUPSIDS);
  720. CREATE INDEX SQL091006084326872
  721. ON WSITEMSMAPPING(ITEMSIDS);
  722. CREATE UNIQUE INDEX SQL091006084327910
  723. ON TABLELOCK(IDS);
  724. CREATE UNIQUE INDEX SQL091006084327911
  725. ON TABLELOCK(CNAME, ROWID);
  726. CREATE INDEX SQL091006084327890
  727. ON TABLELOCK(USERSIDS);
  728. CREATE UNIQUE INDEX SQL091006084328480
  729. ON ITEMSTOACCOUNTS(IDS);
  730. CREATE INDEX SQL091006084328460
  731. ON ITEMSTOACCOUNTS(ITEMSIDS);
  732. CREATE INDEX SQL091006084328461
  733. ON ITEMSTOACCOUNTS(ACCOUNTSIDS);
  734. CREATE UNIQUE INDEX SQL091006084329120
  735. ON TRASHBIN(IDS);
  736. CREATE UNIQUE INDEX SQL091006084329671
  737. ON FILESTOCONTACTS(IDS);
  738. CREATE INDEX SQL091006084329650
  739. ON FILESTOCONTACTS(CONTACTSIDS);
  740. CREATE INDEX SQL091006084329651
  741. ON FILESTOCONTACTS(FILENAME);
  742. CREATE INDEX SQL091006084329670
  743. ON FILESTOCONTACTS(GROUPSIDS);
  744. CREATE UNIQUE INDEX SQL091006084330492
  745. ON TEMPLATES(IDS);
  746. CREATE INDEX SQL091006084330490
  747. ON TEMPLATES(FILENAME);
  748. CREATE INDEX SQL091006084330491
  749. ON TEMPLATES(GROUPSIDS);
  750. CREATE UNIQUE INDEX SQL091006084331263
  751. ON FILESTOITEMS(IDS);
  752. CREATE INDEX SQL091006084331260
  753. ON FILESTOITEMS(ITEMSIDS);
  754. CREATE INDEX SQL091006084331261
  755. ON FILESTOITEMS(FILENAME);
  756. CREATE INDEX SQL091006084331262
  757. ON FILESTOITEMS(GROUPSIDS);
  758. CREATE UNIQUE INDEX SQL091006084332163
  759. ON FILESTOPRODUCTS(IDS);
  760. CREATE INDEX SQL091006084332160
  761. ON FILESTOPRODUCTS(PRODUCTSIDS);
  762. CREATE INDEX SQL091006084332161
  763. ON FILESTOPRODUCTS(FILENAME);
  764. CREATE INDEX SQL091006084332162
  765. ON FILESTOPRODUCTS(GROUPSIDS);
  766. CREATE UNIQUE INDEX SQL091006084333560
  767. ON PLUGINS(IDS);
  768. CREATE INDEX SQL091006084333550
  769. ON PLUGINS(FILENAME);
  770. CREATE INDEX SQL091006084333551
  771. ON PLUGINS(GROUPSIDS);
  772. CREATE UNIQUE INDEX SQL091006084334241
  773. ON PLUGINSTOUSERS(IDS);
  774. CREATE INDEX SQL091006084334230
  775. ON PLUGINSTOUSERS(PLUGINSIDS);
  776. CREATE INDEX SQL091006084334240
  777. ON PLUGINSTOUSERS(GROUPSIDS);
  778. CREATE UNIQUE INDEX SQL091006084334891
  779. ON FORMATSTOUSERS(IDS);
  780. CREATE INDEX SQL091006084334890
  781. ON FORMATSTOUSERS(USERSIDS);
  782. CREATE UNIQUE INDEX SQL091006084335461
  783. ON TEMPLATESTOUSERS(IDS);
  784. CREATE INDEX SQL091006084335450
  785. ON TEMPLATESTOUSERS(USERSIDS);
  786. CREATE INDEX SQL091006084335451
  787. ON TEMPLATESTOUSERS(TEMPLATESIDS);
  788. CREATE INDEX SQL091006084335460
  789. ON TEMPLATESTOUSERS(GROUPSIDS);
  790. CREATE UNIQUE INDEX SQL091006084336332
  791. ON ADDRESSES(IDS);
  792. CREATE INDEX SQL091006084336330
  793. ON ADDRESSES(GROUPSIDS);
  794. CREATE INDEX SQL091006084336331
  795. ON ADDRESSES(CONTACTSIDS);
  796. CREATE UNIQUE INDEX SQL091006084336882
  797. ON REMINDERS(IDS);
  798. CREATE INDEX SQL091006084336880
  799. ON REMINDERS(ITEMSIDS);
  800. CREATE INDEX SQL091006084336881
  801. ON REMINDERS(GROUPSIDS);
  802. CREATE UNIQUE INDEX SQL091006084337581
  803. ON STAGES(IDS);
  804. CREATE INDEX SQL091006084337580
  805. ON STAGES(GROUPSIDS);
  806. ALTER TABLE CONTACTS
  807. ADD CONSTRAINT CONST3
  808. UNIQUE (CNUMBER, GROUPSIDS) ;
  809. ALTER TABLE COUNTRIES
  810. ADD CONSTRAINT SQL091006084312110
  811. UNIQUE (ISO) ;
  812. ALTER TABLE FILES
  813. ADD CONSTRAINT SQL091006084314570
  814. UNIQUE (CNAME) ;
  815. ALTER TABLE GLOBALSETTINGS
  816. ADD CONSTRAINT CONST2
  817. UNIQUE (CNAME, GROUPSIDS) ;
  818. ALTER TABLE GROUPS
  819. ADD CONSTRAINT SQL091006084310810
  820. UNIQUE (CNAME) ;
  821. ALTER TABLE ITEMS
  822. ADD CONSTRAINT CONST7
  823. UNIQUE (CNUMBER, INTTYPE) ;
  824. ALTER TABLE LANGUAGES
  825. ADD CONSTRAINT SQL091006084315171
  826. UNIQUE (LONGNAME) ;
  827. ALTER TABLE LANGUAGES
  828. ADD CONSTRAINT CONST6
  829. UNIQUE (CNAME, GROUPSIDS) ;
  830. ALTER TABLE PRODUCTGROUPS
  831. ADD CONSTRAINT CONST1
  832. UNIQUE (CNAME, GROUPSIDS) ;
  833. ALTER TABLE PRODUCTS
  834. ADD CONSTRAINT CONST8
  835. UNIQUE (CNUMBER, GROUPSIDS) ;
  836. ALTER TABLE TABLELOCK
  837. ADD CONSTRAINT ONE_LOCK
  838. UNIQUE (CNAME, ROWID) ;
  839. ALTER TABLE USERS
  840. ADD CONSTRAINT CONST4
  841. UNIQUE (CNAME, GROUPSIDS) ;
  842. ALTER TABLE WEBSHOPS
  843. ADD CONSTRAINT CONST9
  844. UNIQUE (CNAME, GROUPSIDS) ;
  845. ALTER TABLE WSCONTACTSMAPPING
  846. ADD CONSTRAINT CONST10
  847. UNIQUE (WSCONTACT, GROUPSIDS, WEBSHOPSIDS) ;
  848. ALTER TABLE WSITEMSMAPPING
  849. ADD CONSTRAINT CONST11
  850. UNIQUE (WSITEM, GROUPSIDS, WEBSHOPSIDS) ;
  851. ALTER TABLE ACCOUNTS
  852. ADD CONSTRAINT SQL091006084316450
  853. FOREIGN KEY(GROUPSIDS)
  854. REFERENCES GROUPS(IDS);
  855. ALTER TABLE ADDRESSES
  856. ADD CONSTRAINT SQL091006084336331
  857. FOREIGN KEY(CONTACTSIDS)
  858. REFERENCES CONTACTS(IDS);
  859. ALTER TABLE ADDRESSES
  860. ADD CONSTRAINT SQL091006084336330
  861. FOREIGN KEY(GROUPSIDS)
  862. REFERENCES GROUPS(IDS);
  863. ALTER TABLE COMPS
  864. ADD CONSTRAINT SQL091006084323380
  865. FOREIGN KEY(GROUPSIDS)
  866. REFERENCES GROUPS(IDS);
  867. ALTER TABLE CONTACTS
  868. ADD CONSTRAINT SQL091006084313450
  869. FOREIGN KEY(GROUPSIDS)
  870. REFERENCES GROUPS(IDS);
  871. ALTER TABLE EXPENSES
  872. ADD CONSTRAINT SQL091006084324141
  873. FOREIGN KEY(ACCOUNTSIDS)
  874. REFERENCES ACCOUNTS(IDS);
  875. ALTER TABLE EXPENSES
  876. ADD CONSTRAINT SQL091006084324140
  877. FOREIGN KEY(GROUPSIDS)
  878. REFERENCES GROUPS(IDS);
  879. ALTER TABLE FAVOURITES
  880. ADD CONSTRAINT SQL091006084315881
  881. FOREIGN KEY(GROUPSIDS)
  882. REFERENCES GROUPS(IDS);
  883. ALTER TABLE FAVOURITES
  884. ADD CONSTRAINT SQL091006084315880
  885. FOREIGN KEY(USERSIDS)
  886. REFERENCES USERS(IDS)
  887. ON DELETE CASCADE ;
  888. ALTER TABLE FILES
  889. ADD CONSTRAINT SQL091006084314571
  890. FOREIGN KEY(GROUPSIDS)
  891. REFERENCES GROUPS(IDS);
  892. ALTER TABLE FILESTOCONTACTS
  893. ADD CONSTRAINT SQL091006084329670
  894. FOREIGN KEY(GROUPSIDS)
  895. REFERENCES GROUPS(IDS);
  896. ALTER TABLE FILESTOCONTACTS
  897. ADD CONSTRAINT SQL091006084329651
  898. FOREIGN KEY(FILENAME)
  899. REFERENCES FILES(CNAME)
  900. ON DELETE CASCADE ;
  901. ALTER TABLE FILESTOCONTACTS
  902. ADD CONSTRAINT SQL091006084329650
  903. FOREIGN KEY(CONTACTSIDS)
  904. REFERENCES CONTACTS(IDS)
  905. ON DELETE CASCADE ;
  906. ALTER TABLE FILESTOITEMS
  907. ADD CONSTRAINT SQL091006084331262
  908. FOREIGN KEY(GROUPSIDS)
  909. REFERENCES GROUPS(IDS);
  910. ALTER TABLE FILESTOITEMS
  911. ADD CONSTRAINT SQL091006084331261
  912. FOREIGN KEY(FILENAME)
  913. REFERENCES FILES(CNAME)
  914. ON DELETE CASCADE ;
  915. ALTER TABLE FILESTOITEMS
  916. ADD CONSTRAINT SQL091006084331260
  917. FOREIGN KEY(ITEMSIDS)
  918. REFERENCES ITEMS(IDS)
  919. ON DELETE CASCADE ;
  920. ALTER TABLE FILESTOPRODUCTS
  921. ADD CONSTRAINT SQL091006084332162
  922. FOREIGN KEY(GROUPSIDS)
  923. REFERENCES GROUPS(IDS);
  924. ALTER TABLE FILESTOPRODUCTS
  925. ADD CONSTRAINT SQL091006084332161
  926. FOREIGN KEY(FILENAME)
  927. REFERENCES FILES(CNAME)
  928. ON DELETE CASCADE ;
  929. ALTER TABLE FILESTOPRODUCTS
  930. ADD CONSTRAINT SQL091006084332160
  931. FOREIGN KEY(PRODUCTSIDS)
  932. REFERENCES PRODUCTS(IDS)
  933. ON DELETE CASCADE ;
  934. ALTER TABLE FORMATSTOUSERS
  935. ADD CONSTRAINT SQL091006084334890
  936. FOREIGN KEY(USERSIDS)
  937. REFERENCES USERS(IDS)
  938. ON DELETE CASCADE ;
  939. ALTER TABLE ITEMS
  940. ADD CONSTRAINT SQL091006084316860
  941. FOREIGN KEY(CONTACTSIDS)
  942. REFERENCES CONTACTS(IDS)
  943. ON DELETE CASCADE ;
  944. ALTER TABLE ITEMS
  945. ADD CONSTRAINT SQL091006084316851
  946. FOREIGN KEY(ACCOUNTSIDS)
  947. REFERENCES ACCOUNTS(IDS);
  948. ALTER TABLE ITEMS
  949. ADD CONSTRAINT SQL091006084316850
  950. FOREIGN KEY(GROUPSIDS)
  951. REFERENCES GROUPS(IDS);
  952. ALTER TABLE ITEMSTOACCOUNTS
  953. ADD CONSTRAINT SQL091006084328461
  954. FOREIGN KEY(ACCOUNTSIDS)
  955. REFERENCES ACCOUNTS(IDS)
  956. ON DELETE CASCADE ;
  957. ALTER TABLE ITEMSTOACCOUNTS
  958. ADD CONSTRAINT SQL091006084328460
  959. FOREIGN KEY(ITEMSIDS)
  960. REFERENCES ITEMS(IDS)
  961. ON DELETE CASCADE ;
  962. ALTER TABLE LANGUAGES
  963. ADD CONSTRAINT SQL091006084315172
  964. FOREIGN KEY(FILENAME)
  965. REFERENCES FILES(CNAME)
  966. ON DELETE CASCADE ;
  967. ALTER TABLE LANGUAGES
  968. ADD CONSTRAINT SQL091006084315170
  969. FOREIGN KEY(GROUPSIDS)
  970. REFERENCES GROUPS(IDS);
  971. ALTER TABLE MAILS
  972. ADD CONSTRAINT SQL091006084322551
  973. FOREIGN KEY(USERSIDS)
  974. REFERENCES USERS(IDS)
  975. ON DELETE CASCADE ;
  976. ALTER TABLE MAILS
  977. ADD CONSTRAINT SQL091006084322550
  978. FOREIGN KEY(GROUPSIDS)
  979. REFERENCES GROUPS(IDS);
  980. ALTER TABLE PLUGINS
  981. ADD CONSTRAINT SQL091006084333551
  982. FOREIGN KEY(GROUPSIDS)
  983. REFERENCES GROUPS(IDS);
  984. ALTER TABLE PLUGINS
  985. ADD CONSTRAINT SQL091006084333550
  986. FOREIGN KEY(FILENAME)
  987. REFERENCES FILES(CNAME)
  988. ON DELETE CASCADE ;
  989. ALTER TABLE PLUGINSTOUSERS
  990. ADD CONSTRAINT SQL091006084334240
  991. FOREIGN KEY(GROUPSIDS)
  992. REFERENCES GROUPS(IDS);
  993. ALTER TABLE PLUGINSTOUSERS
  994. ADD CONSTRAINT SQL091006084334230
  995. FOREIGN KEY(PLUGINSIDS)
  996. REFERENCES PLUGINS(IDS)
  997. ON DELETE CASCADE ;
  998. ALTER TABLE PRODUCTLISTITEMS
  999. ADD CONSTRAINT SQL091006084321140
  1000. FOREIGN KEY(ORIGINALPRODUCTSIDS)
  1001. REFERENCES PRODUCTS(IDS)
  1002. ON DELETE CASCADE ;
  1003. ALTER TABLE PRODUCTLISTITEMS
  1004. ADD CONSTRAINT SQL091006084321131
  1005. FOREIGN KEY(PRODUCTLISTSIDS)
  1006. REFERENCES PRODUCTLISTS(IDS)
  1007. ON DELETE CASCADE ;
  1008. ALTER TABLE PRODUCTLISTITEMS
  1009. ADD CONSTRAINT SQL091006084321130
  1010. FOREIGN KEY(GROUPSIDS)
  1011. REFERENCES GROUPS(IDS);
  1012. ALTER TABLE PRODUCTLISTS
  1013. ADD CONSTRAINT SQL091006084320590
  1014. FOREIGN KEY(GROUPSIDS)
  1015. REFERENCES GROUPS(IDS);
  1016. ALTER TABLE PRODUCTS
  1017. ADD CONSTRAINT SQL091006084319612
  1018. FOREIGN KEY(PRODUCTGROUPSIDS)
  1019. REFERENCES PRODUCTGROUPS(IDS);
  1020. ALTER TABLE PRODUCTS
  1021. ADD CONSTRAINT SQL091006084319611
  1022. FOREIGN KEY(GROUPSIDS)
  1023. REFERENCES GROUPS(IDS);
  1024. ALTER TABLE PRODUCTS
  1025. ADD CONSTRAINT SQL091006084319610
  1026. FOREIGN KEY(TAXIDS)
  1027. REFERENCES TAX(IDS);
  1028. ALTER TABLE REMINDERS
  1029. ADD CONSTRAINT SQL091006084336881
  1030. FOREIGN KEY(GROUPSIDS)
  1031. REFERENCES GROUPS(IDS);
  1032. ALTER TABLE REMINDERS
  1033. ADD CONSTRAINT SQL091006084336880
  1034. FOREIGN KEY(ITEMSIDS)
  1035. REFERENCES ITEMS(IDS)
  1036. ON DELETE CASCADE ;
  1037. ALTER TABLE REVENUES
  1038. ADD CONSTRAINT SQL091006084324800
  1039. FOREIGN KEY(ACCOUNTSIDS)
  1040. REFERENCES ACCOUNTS(IDS);
  1041. ALTER TABLE REVENUES
  1042. ADD CONSTRAINT SQL091006084324790
  1043. FOREIGN KEY(GROUPSIDS)
  1044. REFERENCES GROUPS(IDS);
  1045. ALTER TABLE SCHEDULE
  1046. ADD CONSTRAINT SQL091006084318482
  1047. FOREIGN KEY(ITEMSIDS)
  1048. REFERENCES ITEMS(IDS)
  1049. ON DELETE CASCADE ;
  1050. ALTER TABLE SCHEDULE
  1051. ADD CONSTRAINT SQL091006084318481
  1052. FOREIGN KEY(USERSIDS)
  1053. REFERENCES USERS(IDS)
  1054. ON DELETE CASCADE ;
  1055. ALTER TABLE SCHEDULE
  1056. ADD CONSTRAINT SQL091006084318480
  1057. FOREIGN KEY(GROUPSIDS)
  1058. REFERENCES GROUPS(IDS);
  1059. ALTER TABLE STAGES
  1060. ADD CONSTRAINT SQL091006084337580
  1061. FOREIGN KEY(GROUPSIDS)
  1062. REFERENCES GROUPS(IDS);
  1063. ALTER TABLE SUBITEMS
  1064. ADD CONSTRAINT SQL091006084317901
  1065. FOREIGN KEY(GROUPSIDS)
  1066. REFERENCES GROUPS(IDS);
  1067. ALTER TABLE SUBITEMS
  1068. ADD CONSTRAINT SQL091006084317900
  1069. FOREIGN KEY(ITEMSIDS)
  1070. REFERENCES ITEMS(IDS)
  1071. ON DELETE CASCADE ;
  1072. ALTER TABLE TABLELOCK
  1073. ADD CONSTRAINT SQL091006084327890
  1074. FOREIGN KEY(USERSIDS)
  1075. REFERENCES USERS(IDS)
  1076. ON DELETE CASCADE ;
  1077. ALTER TABLE TAX
  1078. ADD CONSTRAINT SQL091006084312890
  1079. FOREIGN KEY(GROUPSIDS)
  1080. REFERENCES GROUPS(IDS);
  1081. ALTER TABLE TEMPLATES
  1082. ADD CONSTRAINT SQL091006084330491
  1083. FOREIGN KEY(GROUPSIDS)
  1084. REFERENCES GROUPS(IDS);
  1085. ALTER TABLE TEMPLATES
  1086. ADD CONSTRAINT SQL091006084330490
  1087. FOREIGN KEY(FILENAME)
  1088. REFERENCES FILES(CNAME)
  1089. ON DELETE CASCADE ;
  1090. ALTER TABLE TEMPLATESTOUSERS
  1091. ADD CONSTRAINT SQL091006084335460
  1092. FOREIGN KEY(GROUPSIDS)
  1093. REFERENCES GROUPS(IDS);
  1094. ALTER TABLE TEMPLATESTOUSERS
  1095. ADD CONSTRAINT SQL091006084335451
  1096. FOREIGN KEY(TEMPLATESIDS)
  1097. REFERENCES TEMPLATES(IDS)
  1098. ON DELETE CASCADE ;
  1099. ALTER TABLE TEMPLATESTOUSERS
  1100. ADD CONSTRAINT SQL091006084335450
  1101. FOREIGN KEY(USERSIDS)
  1102. REFERENCES USERS(IDS)
  1103. ON DELETE CASCADE ;
  1104. ALTER TABLE USERPROPERTIES
  1105. ADD CONSTRAINT SQL091006084322190
  1106. FOREIGN KEY(GROUPSIDS)
  1107. REFERENCES GROUPS(IDS);
  1108. ALTER TABLE USERS
  1109. ADD CONSTRAINT SQL091006084313960
  1110. FOREIGN KEY(GROUPSIDS)
  1111. REFERENCES GROUPS(IDS);
  1112. ALTER TABLE WSCONTACTSMAPPING
  1113. ADD CONSTRAINT SQL091006084326052
  1114. FOREIGN KEY(CONTACTSIDS)
  1115. REFERENCES CONTACTS(IDS)
  1116. ON DELETE CASCADE ;
  1117. ALTER TABLE WSCONTACTSMAPPING
  1118. ADD CONSTRAINT SQL091006084326051
  1119. FOREIGN KEY(GROUPSIDS)
  1120. REFERENCES GROUPS(IDS)
  1121. ON DELETE CASCADE ;
  1122. ALTER TABLE WSCONTACTSMAPPING
  1123. ADD CONSTRAINT SQL091006084326050
  1124. FOREIGN KEY(WEBSHOPSIDS)
  1125. REFERENCES WEBSHOPS(IDS)
  1126. ON DELETE CASCADE ;
  1127. ALTER TABLE WSITEMSMAPPING
  1128. ADD CONSTRAINT SQL091006084326872
  1129. FOREIGN KEY(ITEMSIDS)
  1130. REFERENCES CONTACTS(IDS)
  1131. ON DELETE CASCADE ;
  1132. ALTER TABLE WSITEMSMAPPING
  1133. ADD CONSTRAINT SQL091006084326871
  1134. FOREIGN KEY(GROUPSIDS)
  1135. REFERENCES GROUPS(IDS)
  1136. ON DELETE CASCADE ;
  1137. ALTER TABLE WSITEMSMAPPING
  1138. ADD CONSTRAINT SQL091006084326870
  1139. FOREIGN KEY(WEBSHOPSIDS)
  1140. REFERENCES WEBSHOPS(IDS)
  1141. ON DELETE CASCADE ;
  1142. CREATE TRIGGER CONTACTS_INDEXER1
  1143. AFTER INSERT ON CONTACTS
  1144. REFERENCING NEW AS NEWDATA
  1145. FOR EACH ROW MODE DB2SQL
  1146. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids, 'contacts',newdata.ids,newdata.cnumber||' '||newdata.taxnumber||' '||newdata.title||' '||newdata.country||' '|| newdata.prename||' '|| newdata.cname||' '|| newdata.street||' '||newdata.zip||' '|| newdata.city ||' '||newdata.mainphone||' '||newdata.fax||' '||newdata.mobilephone||' '||newdata.workphone||' '||newdata.mailaddress||' '||newdata.company||' '|| newdata.department||' '||newdata.website||' '||newdata.notes);
  1147. CREATE TRIGGER CONTACTS_INDEXER2
  1148. AFTER UPDATE ON CONTACTS
  1149. REFERENCING NEW AS NEWDATA
  1150. FOR EACH ROW MODE DB2SQL
  1151. DELETE FROM searchindex WHERE dbidentity = 'contacts' AND rowid = newdata.ids;
  1152. CREATE TRIGGER CONTACTS_INDEXER3
  1153. AFTER UPDATE ON CONTACTS
  1154. REFERENCING NEW AS NEWDATA
  1155. FOR EACH ROW MODE DB2SQL
  1156. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'contacts',newdata.ids,newdata.cnumber||' '||newdata.taxnumber||' '||newdata.title||' '||newdata.country||' '|| newdata.prename||' '|| newdata.cname||' '|| newdata.street||' '||newdata.zip||' '|| newdata.city ||' '||newdata.mainphone||' '||newdata.fax||' '||newdata.mobilephone||' '||newdata.workphone||' '||newdata.mailaddress||' '||newdata.company||' '|| newdata.department||' '||newdata.website||' '||newdata.notes);
  1157. CREATE TRIGGER CONTACTS_INDEXER4
  1158. AFTER DELETE ON CONTACTS
  1159. REFERENCING OLD AS NEWDATA
  1160. FOR EACH ROW MODE DB2SQL
  1161. DELETE FROM searchindex WHERE dbidentity = 'contacts' AND rowid = newdata.ids;
  1162. CREATE TRIGGER CONTACTS_TRASH1
  1163. AFTER UPDATE ON CONTACTS
  1164. REFERENCING NEW AS NEWDATA
  1165. FOR EACH ROW MODE DB2SQL
  1166. INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'contacts',newdata.ids,newdata.cnumber||' ('|| newdata.cname||')');
  1167. CREATE TRIGGER CONTACTS_TRASH2
  1168. AFTER DELETE ON CONTACTS
  1169. REFERENCING OLD AS NEWDATA
  1170. FOR EACH ROW MODE DB2SQL
  1171. DELETE FROM trashbin WHERE cname = 'contacts' AND rowid = newdata.ids;
  1172. CREATE TRIGGER EXPENSES_INDEXER1
  1173. AFTER INSERT ON EXPENSES
  1174. REFERENCING NEW AS NEWDATA
  1175. FOR EACH ROW MODE DB2SQL
  1176. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'expenses',newdata.ids,newdata.cname||' '||newdata.dateadded);
  1177. CREATE TRIGGER EXPENSES_INDEXER2
  1178. AFTER UPDATE ON EXPENSES
  1179. REFERENCING NEW AS NEWDATA
  1180. FOR EACH ROW MODE DB2SQL
  1181. DELETE FROM searchindex WHERE dbidentity = 'expenses' AND rowid = newdata.ids;
  1182. CREATE TRIGGER EXPENSES_INDEXER3
  1183. AFTER UPDATE ON EXPENSES
  1184. REFERENCING NEW AS NEWDATA
  1185. FOR EACH ROW MODE DB2SQL
  1186. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'expenses',newdata.ids,newdata.cname||' '||newdata.dateadded);
  1187. CREATE TRIGGER EXPENSES_INDEXER4
  1188. AFTER DELETE ON EXPENSES
  1189. REFERENCING OLD AS NEWDATA
  1190. FOR EACH ROW MODE DB2SQL
  1191. DELETE FROM searchindex WHERE dbidentity = 'expenses' AND rowid = newdata.ids;
  1192. CREATE TRIGGER EXPENSES_TRASH1
  1193. AFTER UPDATE ON EXPENSES
  1194. REFERENCING NEW AS NEWDATA
  1195. FOR EACH ROW MODE DB2SQL
  1196. INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'expenses',newdata.ids,newdata.cname);
  1197. CREATE TRIGGER EXPENSES_TRASH2
  1198. AFTER DELETE ON EXPENSES
  1199. REFERENCING OLD AS NEWDATA
  1200. FOR EACH ROW MODE DB2SQL
  1201. DELETE FROM trashbin WHERE cname = 'expenses' AND rowid = newdata.ids;
  1202. CREATE TRIGGER FILESTOCONTACTS_INDEXER1
  1203. AFTER INSERT ON FILESTOCONTACTS
  1204. REFERENCING NEW AS NEWDATA
  1205. FOR EACH ROW MODE DB2SQL
  1206. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'filestocontacts',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.filename);
  1207. CREATE TRIGGER FILESTOCONTACTS_INDEXER2
  1208. AFTER UPDATE ON FILESTOCONTACTS
  1209. REFERENCING NEW AS NEWDATA
  1210. FOR EACH ROW MODE DB2SQL
  1211. DELETE FROM searchindex WHERE dbidentity = 'filestocontacts' AND rowid = newdata.ids;
  1212. CREATE TRIGGER FILESTOCONTACTS_INDEXER3
  1213. AFTER UPDATE ON FILESTOCONTACTS
  1214. REFERENCING NEW AS NEWDATA
  1215. FOR EACH ROW MODE DB2SQL
  1216. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'filestocontacts',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.filename);
  1217. CREATE TRIGGER FILESTOCONTACTS_INDEXER4
  1218. AFTER DELETE ON FILESTOCONTACTS
  1219. REFERENCING OLD AS NEWDATA
  1220. FOR EACH ROW MODE DB2SQL
  1221. DELETE FROM searchindex WHERE dbidentity = 'filestocontacts' AND rowid = newdata.ids;
  1222. CREATE TRIGGER GROUPS_INDEXER1
  1223. AFTER INSERT ON GROUPS
  1224. REFERENCING NEW AS NEWDATA
  1225. FOR EACH ROW MODE DB2SQL
  1226. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'groups',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded);
  1227. CREATE TRIGGER GROUPS_INDEXER2
  1228. AFTER UPDATE ON GROUPS
  1229. REFERENCING NEW AS NEWDATA
  1230. FOR EACH ROW MODE DB2SQL
  1231. DELETE FROM searchindex WHERE dbidentity = 'groups' AND rowid = newdata.ids;
  1232. CREATE TRIGGER GROUPS_INDEXER3
  1233. AFTER UPDATE ON GROUPS
  1234. REFERENCING NEW AS NEWDATA
  1235. FOR EACH ROW MODE DB2SQL
  1236. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'groups',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded);
  1237. CREATE TRIGGER GROUPS_INDEXER4
  1238. AFTER DELETE ON GROUPS
  1239. REFERENCING OLD AS NEWDATA
  1240. FOR EACH ROW MODE DB2SQL
  1241. DELETE FROM searchindex WHERE dbidentity = 'groups' AND rowid = newdata.ids;
  1242. CREATE TRIGGER ITEMS_INDEXER1
  1243. AFTER INSERT ON ITEMS
  1244. REFERENCING NEW AS NEWDATA
  1245. FOR EACH ROW MODE DB2SQL
  1246. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'items',newdata.ids,newdata.cname||' '||newdata.dateadded);
  1247. CREATE TRIGGER ITEMS_INDEXER2
  1248. AFTER UPDATE ON ITEMS
  1249. REFERENCING NEW AS NEWDATA
  1250. FOR EACH ROW MODE DB2SQL
  1251. DELETE FROM searchindex WHERE dbidentity = 'items' AND rowid = newdata.ids;
  1252. CREATE TRIGGER ITEMS_INDEXER3
  1253. AFTER UPDATE ON ITEMS
  1254. REFERENCING NEW AS NEWDATA
  1255. FOR EACH ROW MODE DB2SQL
  1256. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'items',newdata.ids,newdata.cname||' '||newdata.dateadded);
  1257. CREATE TRIGGER ITEMS_INDEXER4
  1258. AFTER DELETE ON ITEMS
  1259. REFERENCING OLD AS NEWDATA
  1260. FOR EACH ROW MODE DB2SQL
  1261. DELETE FROM searchindex WHERE dbidentity = 'items' AND rowid = newdata.ids;
  1262. CREATE TRIGGER ITEMS_TRASH1
  1263. AFTER UPDATE ON ITEMS
  1264. REFERENCING NEW AS NEWDATA
  1265. FOR EACH ROW MODE DB2SQL
  1266. INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'items',newdata.ids,newdata.cname);
  1267. CREATE TRIGGER ITEMS_TRASH2
  1268. AFTER DELETE ON ITEMS
  1269. REFERENCING OLD AS NEWDATA
  1270. FOR EACH ROW MODE DB2SQL
  1271. DELETE FROM trashbin WHERE cname = 'items' AND rowid = newdata.ids;
  1272. CREATE TRIGGER PRODUCTS_INDEXER1
  1273. AFTER INSERT ON PRODUCTS
  1274. REFERENCING NEW AS NEWDATA
  1275. FOR EACH ROW MODE DB2SQL
  1276. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'products',newdata.ids,newdata.cname||' '||newdata.cnumber||' '||newdata.description||' '||newdata.dateadded);
  1277. CREATE TRIGGER PRODUCTS_INDEXER2
  1278. AFTER UPDATE ON PRODUCTS
  1279. REFERENCING NEW AS NEWDATA
  1280. FOR EACH ROW MODE DB2SQL
  1281. DELETE FROM searchindex WHERE dbidentity = 'products' AND rowid = newdata.ids;
  1282. CREATE TRIGGER PRODUCTS_INDEXER3
  1283. AFTER UPDATE ON PRODUCTS
  1284. REFERENCING NEW AS NEWDATA
  1285. FOR EACH ROW MODE DB2SQL
  1286. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'products',newdata.ids,newdata.cname||' '||newdata.cnumber||' '||newdata.description||' '||newdata.dateadded);
  1287. CREATE TRIGGER PRODUCTS_INDEXER4
  1288. AFTER DELETE ON PRODUCTS
  1289. REFERENCING OLD AS NEWDATA
  1290. FOR EACH ROW MODE DB2SQL
  1291. DELETE FROM searchindex WHERE dbidentity = 'products' AND rowid = newdata.ids;
  1292. CREATE TRIGGER PRODUCTS_TRASH1
  1293. AFTER UPDATE ON PRODUCTS
  1294. REFERENCING NEW AS NEWDATA
  1295. FOR EACH ROW MODE DB2SQL
  1296. INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible, 'products',newdata.ids,newdata.cnumber||' ('|| newdata.cname||')');
  1297. CREATE TRIGGER PRODUCTS_TRASH2
  1298. AFTER DELETE ON PRODUCTS
  1299. REFERENCING OLD AS NEWDATA
  1300. FOR EACH ROW MODE DB2SQL
  1301. DELETE FROM trashbin WHERE cname = 'products' AND rowid = newdata.ids;
  1302. CREATE TRIGGER REVENUES_INDEXER1
  1303. AFTER INSERT ON REVENUES
  1304. REFERENCING NEW AS NEWDATA
  1305. FOR EACH ROW MODE DB2SQL
  1306. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'revenues',newdata.ids,newdata.cname||' '||newdata.dateadded);
  1307. CREATE TRIGGER REVENUES_INDEXER2
  1308. AFTER UPDATE ON REVENUES
  1309. REFERENCING NEW AS NEWDATA
  1310. FOR EACH ROW MODE DB2SQL
  1311. DELETE FROM searchindex WHERE dbidentity = 'revenues' AND rowid = newdata.ids;
  1312. CREATE TRIGGER REVENUES_INDEXER3
  1313. AFTER UPDATE ON REVENUES
  1314. REFERENCING NEW AS NEWDATA
  1315. FOR EACH ROW MODE DB2SQL
  1316. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'revenues',newdata.ids,newdata.cname||' '||newdata.dateadded);
  1317. CREATE TRIGGER REVENUES_INDEXER4
  1318. AFTER DELETE ON REVENUES
  1319. REFERENCING OLD AS NEWDATA
  1320. FOR EACH ROW MODE DB2SQL
  1321. DELETE FROM searchindex WHERE dbidentity = 'revenues' AND rowid = newdata.ids;
  1322. CREATE TRIGGER REVENUES_TRASH1
  1323. AFTER UPDATE ON REVENUES
  1324. REFERENCING NEW AS NEWDATA
  1325. FOR EACH ROW MODE DB2SQL
  1326. INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'revenues',newdata.ids,newdata.cname);
  1327. CREATE TRIGGER REVENUES_TRASH2
  1328. AFTER DELETE ON REVENUES
  1329. REFERENCING OLD AS NEWDATA
  1330. FOR EACH ROW MODE DB2SQL
  1331. DELETE FROM trashbin WHERE cname = 'revenues' AND rowid = newdata.ids;
  1332. CREATE TRIGGER SUBITEMS_INDEXER1
  1333. AFTER INSERT ON SUBITEMS
  1334. REFERENCING NEW AS NEWDATA
  1335. FOR EACH ROW MODE DB2SQL
  1336. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'subitems',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded);
  1337. CREATE TRIGGER SUBITEMS_INDEXER2
  1338. AFTER UPDATE ON SUBITEMS
  1339. REFERENCING NEW AS NEWDATA
  1340. FOR EACH ROW MODE DB2SQL
  1341. DELETE FROM searchindex WHERE dbidentity = 'subitems' AND rowid = newdata.ids;
  1342. CREATE TRIGGER SUBITEMS_INDEXER3
  1343. AFTER UPDATE ON SUBITEMS
  1344. REFERENCING NEW AS NEWDATA
  1345. FOR EACH ROW MODE DB2SQL
  1346. INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'subitems',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded);
  1347. CREATE TRIGGER SUBITEMS_INDEXER4
  1348. AFTER DELETE ON SUBITEMS
  1349. REFERENCING OLD AS NEWDATA
  1350. FOR EACH ROW MODE DB2SQL
  1351. DELETE FROM searchindex WHERE dbidentity = 'subitems' AND rowid = newdata.ids;
  1352. CREATE TRIGGER THRASH_HANDLER1
  1353. AFTER INSERT ON TRASHBIN
  1354. FOR EACH STATEMENT MODE DB2SQL
  1355. DELETE FROM trashbin WHERE deleteme = 0;
  1356. CREATE TRIGGER THRASH_HANDLER2
  1357. AFTER INSERT ON TRASHBIN
  1358. FOR EACH STATEMENT MODE DB2SQL
  1359. DELETE FROM trashbin WHERE ids IN (SELECT ids FROM trashbin WHERE EXISTS( SELECT ids FROM trashbin AS tmptable WHERE trashbin.cname = tmptable.cname AND trashbin.rowid = tmptable.rowid HAVING trashbin.ids < MAX(tmptable.ids) ) );
  1360. INSERT INTO GROUPS(CNAME, DESCRIPTION, DEFAULTS, GROUPSIDS, DATEADDED, RESERVE1, INTADDEDBY, HIERARCHYPATH, RESERVE2)
  1361. VALUES( 'All Groups', 'This group is visible to everyone.', NULL, 0, '2009-04-03', NULL, 0, NULL, NULL);
  1362. INSERT INTO ACCOUNTS(INTACCOUNTCLASS, CNAME, DESCRIPTION, TAXVALUE, DATEADDED, INTADDEDBY, INTPARENTACCOUNT, GROUPSIDS, INVISIBLE, INTACCOUNTTYPE, INTPROFITFID, INTTAXFID, INTTAXUID, FRAME, HIERARCHYPATH)
  1363. VALUES( 0, 'All Accounts', 'This account is the parent account of all account frames.', 0.0, '2009-04-03', 0, 0, 1, 0, 0, 0, 0, 0, 'builtin', NULL);
  1364. INSERT INTO ACCOUNTS(INTACCOUNTCLASS, CNAME, DESCRIPTION, TAXVALUE, DATEADDED, INTADDEDBY, INTPARENTACCOUNT, GROUPSIDS, INVISIBLE, INTACCOUNTTYPE, INTPROFITFID, INTTAXFID, INTTAXUID, FRAME, HIERARCHYPATH)
  1365. VALUES( 0, 'Expenses', '', 0.0, '2009-04-03', 0, 1, 1, 0, 2, 0, 0, 0, 'builtin', NULL);
  1366. INSERT INTO ACCOUNTS(INTACCOUNTCLASS, CNAME, DESCRIPTION, TAXVALUE, DATEADDED, INTADDEDBY, INTPARENTACCOUNT, GROUPSIDS, INVISIBLE, INTACCOUNTTYPE, INTPROFITFID, INTTAXFID, INTTAXUID, FRAME, HIERARCHYPATH)
  1367. VALUES( 0, 'Income', '', 0.0, '2009-04-03', 0, 1, 1, 0, 3, 0, 0, 0, 'builtin', NULL);
  1368. INSERT INTO COUNTRIES(CNAME, ISO, GROUPSIDS, RESERVE1, RESERVE2)
  1369. VALUES( 'Deutschland', 276, 1, NULL, NULL);
  1370. INSERT INTO GLOBALSETTINGS(CNAME, GROUPSIDS, VALUE)
  1371. VALUES( 'yabs_dbversion', 1, '1.0');
  1372. INSERT INTO PRODUCTGROUPS(CNAME, DESCRIPTION, DEFAULTS, GROUPSIDS, PRODUCTGROUPSIDS, DATEADDED, HIERARCHYPATH, RESERVE1, INTADDEDBY, RESERVE2)
  1373. VALUES( 'All Products', 'This product group is visible to everyone.', NULL, 0, 0, '2009-04-03', NULL, NULL, 0, NULL);
  1374. INSERT INTO SEARCHINDEX(DBIDENTITY, GROUPSIDS, ROWID, TEXT)
  1375. VALUES( 'groups', 0, 1, 'All Groups This group is visible to everyone. 2009-04-03');
  1376. INSERT INTO TAX(CNAME, TAXVALUE, IDENTIFIER, GROUPSIDS, COUNTRY, DATEADDED, INTADDEDBY, INVISIBLE, RESERVE1, RESERVE2)
  1377. VALUES( 'Default 0%', 0.0, 'Default 0%', 1, NULL, '2009-04-03', 0, 0, NULL, NULL);
  1378. INSERT INTO TAX(CNAME, TAXVALUE, IDENTIFIER, GROUPSIDS, COUNTRY, DATEADDED, INTADDEDBY, INVISIBLE, RESERVE1, RESERVE2)
  1379. VALUES( 'Default 19%', 19.0, 'Default 19%', 1, NULL, '2009-04-03', 0, 0, NULL, NULL);
  1380. INSERT INTO TAX( CNAME, TAXVALUE, IDENTIFIER, GROUPSIDS, COUNTRY, DATEADDED, INTADDEDBY, INVISIBLE, RESERVE1, RESERVE2)
  1381. VALUES( 'Default 7%', 7.0, 'Default 7%', 1, NULL, '2009-04-03', 0, 0, NULL, NULL);
  1382. INSERT INTO USERS(CNAME, GROUPSIDS, INTDEFAULTACCOUNT, COMPSIDS, INTDEFAULTSTATUS, FULLNAME, PASSWORD, LAF, LOCALE, DEFCOUNTRY, MAIL, LANGUAGE, INTHIGHESTRIGHT, ISENABLED, ISRGROUPED, ISLOGGEDIN, DATELASTLOG, DATEADDED, INTADDEDBY, INVISIBLE, RESERVE1, RESERVE2)
  1383. VALUES( 'admin', 1, 1, 0, 1, 'Administrator', '5f4dcc3b5aa765d61d8327deb882cf99', 'com.sun.java.swing.plaf.windows.WindowsLookAndFeel', 'en_GB', '', '', 'buildin_en', 0, 1, 0, 0, '2009-10-06', '2009-04-03', 0, 0, NULL, NULL);