On Fri, Apr 18, 2014 at 12:53 AM, Simon Slavin <slav...@bigfraud.org> wrote:
> On 17 Apr 2014, at 11:24pm, David Clark <quincy...@yahoo.com> wrote:
>> If I have a table of
>> field1 varchar(25)
>> field2 varchar(50)
>> field3 varchar(75)
>
> You don't. SQLite does not support a datatype of varchar(). Fields you
> declare like that will be implemented as TEXT fields and handled the same as
> any other TEXT field, and the length of the contents ignored.
>
>> I know sqlite does not enforce limits, but in my program it would be useful
>> if I could find the declared lengths of
>> 25, 50 and 75 in this case. How might I do that in sqlite?
>
> Technically you could retrieve and parse the CREATE command used to make the
> TABLE and figure them out. But since they aren't going to be enforced
> there's little point.
>
> There are ways to enforce field length limits entirely within SQLite but
> they're complicated so post again if you want me to explain TRIGGERs.
No need to resort to triggers. A simple check constraint will do:
C:\Users\DDevienne>sqlite3
SQLite version 3.8.3.1 2014-02-11 14:52:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t (a varchar(2), b varchar(2) check (length(b) < 3));
sqlite> insert into t (a) values ('a'), ('aa'), ('aaa');
sqlite> select * from t;
a|
aa|
aaa|
sqlite> insert into t (b) values ('b'), ('bb'), ('bbb');
Error: CHECK constraint failed: t
sqlite> select * from t;
a|
aa|
aaa|
sqlite> insert into t (b) values ('b'), ('bb');
sqlite> select * from t;
a|
aa|
aaa|
|b
|bb
sqlite>
You still have to explicitly hard-code the max-length to check against though.
Perhaps you could create a function that takes 3 params, table-name,
column-name (or index), and column value, and use
sqlite3_column_decltype as Igor mentioned, to generate a simple select
column-name from table-name and introspect the varchar(x) declared
type to infer the max length (caching the result with aux-data?), but
that seems like too much work for little gain to me. --DD
PS: I didn't check whether length() returns the byte-length or the
char-length (unicode codepoint length), which matters if you go beyond
ASCII and care about Oracle-like VARCHAR2(64 CHAR) vs VARCHAR2(64).
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
