Using SQLi Import with Oracle

SQLi Import is a nice eZ Publish extension that allows you to develop data importers in a quick and elegant way.

Sadly, it comes only with MySQL support, but it takes only a minute to make it work with Oracle too. So here’s the SQL code I’ve used to make it happy 🙂

(tested with SQLi Import 1.2.0 and eZ Publish 4.4)

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
CREATE TABLE sqliimport_scheduled (
  id INT NOT NULL,
  handler VARCHAR(50),
  label VARCHAR(255),
  options_serialized CLOB,
  frequency VARCHAR(30) NOT NULL,
  NEXT INT DEFAULT 0,
  user_id INT,
  requested_time INT,
  is_active SMALLINT DEFAULT 0,
  manual_frequency INT DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE sqliimport_item (
  id INT NOT NULL,
  handler VARCHAR(50),
  options_serialized CLOB,
  user_id INT,
  requested_time INT DEFAULT 0,
  STATUS SMALLINT DEFAULT 0,
  percentage_int SMALLINT DEFAULT 0,
  TYPE SMALLINT DEFAULT 1,
  progression_notes CLOB,
  process_time INT DEFAULT 0,
  scheduled_id INT,
  PRIMARY KEY (id)
);

CREATE SEQUENCE se_sqliimport_item;
CREATE SEQUENCE se_sqliimport_scheduled;

CREATE OR REPLACE TRIGGER tr_sqliimport_scheduled_id
BEFORE INSERT ON sqliimport_scheduled
FOR EACH ROW
WHEN ( NEW.ID IS NULL )
BEGIN
    SELECT se_sqliimport_scheduled.NEXTVAL
    INTO :NEW.ID
    FROM dual;
END;
/

CREATE OR REPLACE TRIGGER tr_sqliimport_item_id
BEFORE INSERT ON sqliimport_item
FOR EACH ROW
WHEN ( NEW.ID IS NULL )
BEGIN
    SELECT se_sqliimport_item.NEXTVAL
    INTO :NEW.ID
    FROM dual;
END;
/

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *