[sqlite] Pragma automatic_index and ROWID: which one is really responsible for automatic indexes?

[R Smith]

On 2016/01/28 10:08 PM, R Smith wrote:
>
>
> -- Insert Loop start:
> -- This will simply fail if the PK already exists, else start the line 
> with 0 count.
> INSERT OR IGNORE INTO element_attribute_values VALUES (:element, 
> :attribute, :value, 0);
>
> -- This might be faster since it uses only one lookup loop, but it 
> might also not be.
> -- If you share a list of example data to be inserted, we can find a 
> faster way. Try it and let us know...
> WITH EAV(id,icnt) AS (
>   SELECT rowid,cnt+1
>     FROM element_attribute_values
>    WHERE (element = :element) AND (attribute = :attribute) AND (value 
> = :value)
>    LIMIT 1
> )
> UPDATE element_attribute_values SET cnt = (SELECT icnt FROM EAV)
>  WHERE rowid = (SELECT id FROM EAV)
> ;
> -- Insert Loop end.
>

Upon re-reading I realised my descriptions were confusing. To be clear, 
this is the entire loop SQL that should form part of your program:

    -- Insert Loop start:
    INSERT OR IGNORE INTO element_attribute_values VALUES (:element,
    :attribute, :value, 0);

    WITH EAV(id,icnt) AS (
       SELECT rowid,cnt+1
         FROM element_attribute_values
        WHERE (element = :element) AND (attribute = :attribute) AND
    (value = :value)
        LIMIT 1
    )
    UPDATE element_attribute_values SET cnt = (SELECT icnt FROM EAV)
      WHERE rowid = (SELECT id FROM EAV)
    ;
    -- Insert Loop end.