Re: [sqlite] The upcoming alter table rename column
Still doesn't work when saving directly to file. Nice
though. Modified version follows. Thanks.
danap.
Fixes/Adds:
1. Header comment added --.
2. Added drop IF EXISTS.
3. Each CREATE VIEW added space after AS.
4. Placed end of statement semicolon directly after parenthesis.
5. Has only \n for end of lines.
-- Schema Info Views
--
-- This is a set of views that supply queryable Schema information for
-- SQLite DBs in
-- table format.
--
DROP VIEW IF EXISTS SysIndexColumns;
DROP VIEW IF EXISTS SysIndexes;
DROP VIEW IF EXISTS SysColumns;
DROP VIEW IF EXISTS SysObjects;
CREATE VIEW SysObjects AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE
FROM (SELECT type AS ObjectType, name AS ObjectName
FROM sqlite_master
WHERE type IN ('table', 'view', 'index')
);
CREATE VIEW SysColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, ColumnID
COLLATE NOCASE,
ColumnName COLLATE NOCASE, Type COLLATE NOCASE, Affinity COLLATE
NOCASE,
IsNotNull, DefaultValue, IsPrimaryKey
FROM (SELECT ObjectType, ObjectName, cid AS ColumnID, name AS
ColumnName, type AS Type,
CASE
WHEN trim(type) = '' THEN 'Blob'
WHEN instr(UPPER(type),'INT' )>0 THEN 'Integer'
WHEN instr(UPPER(type),'CLOB')>0 THEN 'Text'
WHEN instr(UPPER(type),'CHAR')>0 THEN 'Text'
WHEN instr(UPPER(type),'TEXT')>0 THEN 'Text'
WHEN instr(UPPER(type),'BLOB')>0 THEN 'Blob'
WHEN instr(UPPER(type),'REAL')>0 THEN 'Real'
WHEN instr(UPPER(type),'FLOA')>0 THEN 'Real'
WHEN instr(UPPER(type),'DOUB')>0 THEN 'Real'
ELSE 'Numeric'
END AS Affinity,
"notnull" AS IsNotNull, dflt_value as DefaultValue, pk
AS IsPrimaryKey
FROM SysObjects
JOIN pragma_table_info(ObjectName)
);
CREATE VIEW SysIndexes AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
IndexID, IsUnique COLLATE NOCASE, IndexOrigin COLLATE NOCASE,
isPartialIndex
FROM (SELECT ObjectType,ObjectName,name AS IndexName, seq AS IndexID,
"unique" AS isUnique, origin AS IndexOrigin, partial AS
isPartialIndex
FROM SysObjects
JOIN pragma_index_list(ObjectName)
);
CREATE VIEW SysIndexColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
IndexColumnSequence, ColumnID, ColumnName COLLATE NOCASE,
isDescendingOrder, Collation, isPartOfKey
FROM (SELECT ObjectType, ObjectName, IndexName, seqno AS
IndexColumnSequence, cid AS ColumnID,
name AS ColumnName, "desc" AS isDescendingOrder, coll AS
Collation, key AS isPartOfKey
FROM SysIndexes
JOIN pragma_index_xinfo(IndexName)
);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA case_sensitive_like
Chris Locke wrote: > Is there a reason for it being write only? This pragma just installs a different LIKE() function, and there is no easy mechanism to read the function pointer back. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA case_sensitive_like
Thanks J - not entirely sure how that's helpful - I know how to call the pragma, but I was enquiring as to whether there was a way of reading a write-only pragma (which sounds nonsense now that I've written that down!!) Thanks, Chris On Thu, Aug 16, 2018 at 3:16 PM J Decker wrote: > https://www.sqlite.org/pragma.html#pragma_pragma_list > > A pragma can take either zero or one argument. The argument is may be > either in parentheses or it may be separated from the pragma name by an > equal sign. The two syntaxes yield identical results. In many pragmas, the > argument is a boolean. The boolean can be one of: > > *1 yes true on0 no false off* > > Keyword arguments can optionally appear in quotes. (Example: 'yes' > [FALSE].) > Some pragmas takes a string literal as their argument. When pragma takes a > keyword argument, it will usually also take a numeric equivalent as well. > For example, "0" and "no" mean the same thing, as does "1" and "yes". When > querying the value of a setting, many pragmas return the number rather than > the keyword. > looks like , from the docs, ` pragma *case_sensitive_like` should rteturn > the value; a pragma without a value. > there's also a pragma pragma_list which (if compiled in) will return all > available pragmas > > On Thu, Aug 16, 2018 at 6:16 AM Chris Locke > wrote: > > > While the pragma *case_sensitive_like *can be set, there doesn't seem to > be > > a way to read it. > > Is there a reason for it being write only? > > A hacky workaround is to use the SQL, "select 'x' like 'X' ", but is > there > > a better way? > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA case_sensitive_like
https://www.sqlite.org/pragma.html#pragma_case_sensitive_like Basically nope, there isn't a better way. There is no 0 argument version of the pragma, and there is no table-valued function as it's a pragma with a side effect. So running a small query where you know what the answer is is currently the only way. Also means that if you're going to change it, you want to run that test query again afterwards to make sure it stuck. If there's a typo in your pragma call then it gets silently ignored, and there's no return value when setting it. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of J Decker Sent: Thursday, August 16, 2018 10:16 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] PRAGMA case_sensitive_like ... looks like , from the docs, ` pragma *case_sensitive_like` should rteturn the value; a pragma without a value. ... On Thu, Aug 16, 2018 at 6:16 AM Chris Locke wrote: > While the pragma *case_sensitive_like *can be set, there doesn't seem to be > a way to read it. > Is there a reason for it being write only? > A hacky workaround is to use the SQL, "select 'x' like 'X' ", but is there > a better way? > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA case_sensitive_like
https://www.sqlite.org/pragma.html#pragma_pragma_list A pragma can take either zero or one argument. The argument is may be either in parentheses or it may be separated from the pragma name by an equal sign. The two syntaxes yield identical results. In many pragmas, the argument is a boolean. The boolean can be one of: *1 yes true on0 no false off* Keyword arguments can optionally appear in quotes. (Example: 'yes' [FALSE].) Some pragmas takes a string literal as their argument. When pragma takes a keyword argument, it will usually also take a numeric equivalent as well. For example, "0" and "no" mean the same thing, as does "1" and "yes". When querying the value of a setting, many pragmas return the number rather than the keyword. looks like , from the docs, ` pragma *case_sensitive_like` should rteturn the value; a pragma without a value. there's also a pragma pragma_list which (if compiled in) will return all available pragmas On Thu, Aug 16, 2018 at 6:16 AM Chris Locke wrote: > While the pragma *case_sensitive_like *can be set, there doesn't seem to be > a way to read it. > Is there a reason for it being write only? > A hacky workaround is to use the SQL, "select 'x' like 'X' ", but is there > a better way? > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Lite plus
Hi all, I'd like to announce a small project called liteplus https://gitlab.com/daitangio/sqlite_ora_functions Liteplus provide a bunch of extra function to sqlite, written in python. You got some common Oracle functions (like nvl,decode, etc) Feel free to comment, open issue/suggestions an give me your feedback :) Please excuse typs as Sent from my iPhone ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA case_sensitive_like
While the pragma *case_sensitive_like *can be set, there doesn't seem to be a way to read it. Is there a reason for it being write only? A hacky workaround is to use the SQL, "select 'x' like 'X' ", but is there a better way? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Safe sqlite over remote filesystem?
On 15 August 2018 at 14:12, Wout Mertens wrote: > On Tue, Aug 14, 2018 at 6:28 PM Rowan Worth wrote: > > > FWIW in the building I work in we have 20-30 users hitting around a dozen > > SQLite DBs 8 hours a day 5 days a week, with all DBs served over nfs. > > > Multiple writers? I presume you use WAL mode? > Yes, all clients read and write the DBs. No we don't use WAL mode, as the clients all run on different machines (WAL only works for multiple clients on the same machine). > We plan to migrate to an actual SQL server > > for that reason, but please don't take it as a criticism of SQLite - I > > think it does a marvelous job in a scenario it definitely wasn't designed > > for. > > > > Before you do that, did you see bedrockdb? http://bedrockdb.com/ > (Although it looks like the github repo is being a little bit ignored by > Expensify) > I've given a reasonable amount of thought towards some kind of "server-fied" SQLite backend, but the main problem I'm facing is that our application's DB layer is very coupled to SQLite and its library bindings. The changes/abstractions required for the application to talk to such a backend are the same amount of work to implement as having it talk to an SQL server. Actually replication might allow that work to be bypassed, but there's a lot of unknowns there as to failure modes and how to manage the machines involved in replication as users drop in and out of different DBs in an ad-hoc fashion. Also on the infrastructure side we have a push towards net-booted diskless nodes... -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
