[sqlite] autoincrement

[R Smith]

On 2016/04/13 4:58 PM, Igor Korot wrote:
>   Hi,,
>
> On Wed, Apr 13, 2016 at 10:54 AM, J Decker <d3ck0r at gmail.com> wrote:
>> Yes, you can get the create statement from sqlite_master table
> I was kind of hoping for a simpler solution so that not to parse "CREATE 
> TABLE"
> statement...
>
> Well, I guess I will have to.
>
> Thank you.

Hi Igor,

Note that SQLite will auto increment PK fields whether AUTOINCREMENT is 
present or not, it just ensures non-repeats and guaranteed increments 
when present.

Either way, this following query will give the column in any table that 
is AUTOINCREMENTed (if any), just replace 'AutoIncTable' with your table 
name (or bind it).

    WITH WS(WhSpc) AS (
         SELECT char(9)||char(10)||char(13)||char(32) -- White space chars
    ), AI(idxStart, idxAuto, sql) AS (
         SELECT instr(sql,'('), instr(upper(sql),' AUTOINCREMENT'), sql
           FROM "sqlite_master" WHERE type = 'table' AND tbl_name =
    'AutoIncTable' -- < Replace your table here
    ), PRS(idxNextComma, remainText) AS (
         SELECT 1, ','||trim(substr(sql,idxStart+1,idxAuto-idxStart),WhSpc)
           FROM AI,WS
          WHERE idxStart>1 AND idxAuto>idxStart
       UNION ALL
         SELECT instr(substr(trim(remainText,WhSpc),
    idxNextComma+1),','), substr(trim(remainText,WhSpc), idxNextComma+1)
           FROM PRS,WS
          WHERE idxNextComma > 0
    ), WRD(idxSpace, remText) AS (
         SELECT 999, remainText FROM PRS WHERE idxNextComma = 0
       UNION ALL
         SELECT instr(trim(substr(remText, 1, idxSpace)),' '),
    trim(substr(remText, 1, idxSpace)) FROM WRD WHERE idxSpace > 0
    )
    SELECT trim(remText,'['' "`]') AS AutoInc_Col FROM WRD WHERE
    idxSPace = 0



Note 1 - You CAN break it by adding /* comments */  in between the 
column name and the AUTOINCREMENT keyword in your table definition, and 
then have the comment contain the said keyword or commas, but this 
should not ever be the case in a normal table, less so if you control 
the schema.

Note 2 - Parsing out comments first would make it foolproof, but I will 
leave that exercise for if you really need it and/or fancy actually 
using this method.

Note 3 - It's not crazy efficient, but usually the amount of tables in a 
schema is not that large.


Cheers,
Ryan