SQLite Forum

I remember POV-Ray was in space, http://www.povray.org/posters/ They build a special 'stop go stop go' feature in it to throttle the rendering. Getting rid of heat from the processor is a problem. Can imagine such problems happen with the iPad, unless modified.

filesystem or SQLite authorizer

What I meant is if it was possible to force any process that I may not be aware of (3rd party) to access the DB only in readonly mode when my app uses it.

Could user access rights to the db file be of help here?

[...] blocking anyone else from writing while still allowing them to both connect and read.

Could that become an option in SQLite? A "blocking writer (+read) connection",so you won't have to do the IMMEDIATE dance? This as the question seems to come up every now and then. No idea how useful it actually would be.

"Everything" gets updates. Books not. I don't mean a version 2.0 or a rewrite. Why not publish updates on a book? A new chapter on new features every 6-18 months. Maybe a touch up or rewrite of a chapter every now and then. Should be doable with the E-book formats. (and with a better e-book format they could be less linear)

With SQLites backwards compatibility it seems like a fine candidate to me for an updatable book. Older content does not go stale (fast).

Cheers! Enjoy your beer,

Ingo

That answers my question. Thanks.

'Ve been looking at that too Ryan. Speed is no issue here, nor reverting to a previous situation. If that needs to be done then manually so that it shows up in the time line, with proper comments. It will be used for tracking changes in processes and changes of state in processes. For example in an order management system, the change from offer to order to invoice. Or the change of specification of a batch or batchnumber of raw materials. Kind of mini 'time lines'.

By the answers I'm not sure whether my first post was all that clear.

Second attempt; The example below may not be flawless, just to show what I was thinking. The 'old' row is copied to a new test_id and references the old_test_id that is also in use in other tables. Now the 'old' row can be updated with new content. Query for an "id" in historic gives all previous versions, sort by ts_eol. For current version create an index (test_id, ts_eol is Null)

CREATE TABLE test(
    test_id INTEGER PRIMARY KEY NOT NULL
    content TEXT NOT NULL
    historic INTEGER DEFAULT NULL REFERENCES test(test_id)
    ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ts_eol TEXT DEFAULT NULL,
);

CREATE TRIGGER IF NOT EXISTS update_test
BEFORE UPDATE OF content ON test
BEGIN  
    INSERT INTO test (content, historic, ts_from, ts_eol)
    SELECT old.content, old.test_id, old.ts_from, CURRENT_TIMESTAMP
    ;
END
;

While looking at keeping history for some tables one method often mentioned goes as follows "If data is modified or deleted, the original record is not updated; instead a new record is created that holds the latest information. The new (most recent) record becomes the live record (active), while all previous records form the history."

Is there a reason to not do the following? "If data is modified, a new record is created that holds the old information and references the original record. The original record is updated; The new (most recent) record becomes the historic record. Timestamps are set accordingly. If a record is deleted only the timestamps are set (soft delete).

The reason, ID's stay the same for the active record as they are referenced in several places. ID's can be kept unique.

Joe,

It's something I've build from ground up some years ago. I actually don't like spreadsheets at all. Looked at open source systems for some time, but they all seem to have problems with recipe based processes followed by bom based packaging etc. Also updating these systems, especially when using your own programmed modules, is a pain. They have to make money from something. The whole thing runs fine as is. The queries may be a bit more complex as with multiple tables. It also works nice with 'throughput accounting'. Live data showing yields and profits instead of costs.

Cheers.

Thanks Joe!

Just to give a bit more background, it's part of a bigger thing. I used order_header, order_body, due to lack of a better name. form_header, form_body may be better, but is also very general. As the structure and the data for sales- or purchase order, invoice etc is all the same I didn't differentiate to different tables. There is a complete contact database. Contacts can have an account_number, or not. With an account_number they can have several roles like customer, supplier, employee, tax-collector etc. The type of "order" header they are in defines their role. It's a one man shop here, but I could create a monthly order to pay an employee. There is a complete database for 'items'. These can be bought ones, produced intermediates and final ones. The are partley produced on recipe and partly on bom. They all also have an account_number. All transaction, also internal stock moves are managed through the balance sheets, so materials, labour, process steps and money are always directly linked. As soon as the state of anything changes a copy of the 'old version' is made and references to the new version. So for everything a time-line can be constructed. The form_header_id stays the same when an offer changes to an order and from there to an invoice.

Ingo

What I cobbled together many moons ago for some simple stuff. Everything, item, contact, that can apper in the balance sheets https://gist.github.com/NYKevin/9433376 has an account(account_id). Then there is a lot of (trigger)machinery and a bit more in the tables for history of changes.

Comments welcome.

CREATE TABLE IF NOT EXISTS order_head(
 order_number INTEGER NOT NULL UNIQUE PRIMARY KEY,
     relation INTEGER NOT NULL REFERENCES account(account_id),
   order_type INTEGER NOT NULL REFERENCES order_type(id),
      ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
       ts_eol TEXT DEFAULT NULL
);


CREATE TABLE order_body(
 order_number INTEGER REFERENCES order_head(order_id),
   order_line INTEGER NOT NULL,
         item INTEGER NOT NULL REFERENCES account(account_id),
     quantity REAL NOT NULL,
        value REAL NOT NULL,
quantity_recv REAL,
   value_recv REAL,
      ts_recv TEXT,
        state INTEGER NOT NULL REFERENCES order_state(id) DEFAULT 1,
      ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
       ts_eol TEXT DEFAULT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS order_body_number_line
ON order_body(order_number, order_line)
;

CREATE TABLE order_type(
           id INTEGER NOT NULL PRIMARY KEY,
         type TEXT NOT NULL UNIQUE,
        short TEXT NOT NULL UNIQUE,
           ts TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
       ts_eol TEXT DEFAULT NULL
);

CREATE TABLE order_state(
           id INTEGER NOT NULL PRIMARY KEY,
        state TEXT,
      ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
       ts_eol TEXT DEFAULT NULL
);

For me, quote, invoice, purchase order, sales order and manufacturing order are all the 'same', only the head is a bit different. So I have a table order_head and order_lines and order_type. This plus the customer- or supplier data are an invoice or order etc. Type can be changed from quote to order or invoice or it can be created as an order directly

Dank je Kees for confirmation. couldn't find an example using a 'tempstore' this way, or didn't use the right lingo when searching.

It is not possible to create or use temp tables or cte's inside (instead of) triggers. Yet I need a storage for intermediate results, generated within the trigger. So I created a normal table to be used as a 'tempstore' for these kind of data. At the end of the trigger the generated data are deleted from the table, leaving it empty for the next use.

For as I can see now there will be only one connection that writes. Is this sound/safe, is there an other/better way?

It's an abstraction, not genealogy. It's about raw materials and (intermediate) products that are the leafs. In packing.carton.6_bottles (index 3.18.2) and packing.carton.12_bottles (index 3.18.5) the leaves have to be unique. The names can appear in other branches. Even packing.carton.6_bottles.6_bottles (index 3.18.2.27)could exist. packing.carton.6_bottles (index 3.22.2) should not be possible

I will study more, Thank you.

Ingo

How do I prevent that, in a closure table, a parent has no children with duplicate 'names'?

I think I need a trigger for that, but cannot get it to work and I'm not sure I'm on the right path with this. Some guidance is welcome.

TIA,

Ingo

PRAGMA writable_schema = ON;
INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)
VALUES('table','asset_closure','asset_closure',0,'CREATE VIRTUAL TABLE IF NOT EXISTS asset_closure 
  USING transitive_closure (
    tablename="asset_tree",
    idcolumn="id",
    parentcolumn="parent_id")');
PRAGMA writable_schema = OFF;

CREATE TABLE IF NOT EXISTS asset_tree(
          id INTEGER PRIMARY KEY NOT NULL,   
   parent_id INTEGER REFERENCES asset_tree(id) 
             CHECK(id <> parent_id),
  CONSTRAINT u_id_parent UNIQUE(id, parent_id)
);

CREATE TABLE IF NOT EXISTS asset_node(
     node_id REFERENCES asset_tree(id),
        node TEXT
);

CREATE INDEX idx_parent_id ON asset_tree(parent_id);
CREATE INDEX idx_node_id ON asset_node(node_id);
CREATE INDEX idx_node ON asset_node(node);


-- trigger to prevent a parent to have children with teh same 'name'
CREATE TRIGGER no_duplicate_childname_per_parent
BEFORE INSERT
    ON asset_node
  WHEN asset_node.node.old IN (
       --selects the parents childrens names (asset_node.node)
       --where parent_id = last_insert_rowid()
       --temp table
       SELECT asset_node.node AS child 
         FROM asset_tree                                                      
         JOIN asset_node
           ON asset_tree.id = asset_node.node_id
        WHERE asset_tree.parent_id = (
              SELECT parent_id
                FROM asset_tree
               WHERE id = last_insert_rowid()
        )
  )
 BEGIN
       SELECT(RAISE (FAIL, 'A parent can not have two children with the same name'));    
   END;


-- Insert a single node
PRAGMA temp_store = 2;

BEGIN TRANSACTION;

CREATE TEMP TABLE _last_row(
       lr INTEGER,
       value INTEGER
);

INSERT INTO asset_tree (parent_id)
VALUES (NULL) --null for root items, else parent_id
;

INSERT INTO _last_row
VALUES (1, last_insert_rowid())
;

INSERT  INTO asset_node(node_id, node)
VALUES 
       ((SELECT value FROM _last_row WHERE lr = 1), 'new inserted asset')
;

DROP TABLE _last_row;       

END TRANSACTION;