[sqlite] Minor SQLDIFF issue
tonyp at acm.org wrote: > Doing SQLDIFF against a non-existent database (e.g., due to a typo) produces > a diff which is a series of DROP TABLE ... and also creates an empty file by > that name. sqlite3_open() automatically creates the database file if it does not yet exist. (This is the only way to create a new database.) > Shouldn?t it instead give an error that there is no such database? Yes. sqlite3_open_v2() would allow to disable this behaviour. Regards, Clemens
[sqlite] Creating system table
On 12 Mar 2016, at 9:59pm, Clemens Ladisch wrote: > This is documented in the documentation: > http://www.sqlite.org/fileformat2.html#intschema To that documentation should be added sqlite_users. Simon.
[sqlite] Creating system table
Igor Korot wrote: > OK, so how many system tables SQLite has? This is documented in the documentation: http://www.sqlite.org/fileformat2.html#intschema Regards, Clemens
[sqlite] hexadecimal conversion on select query
maybe ?
https://www.sqlite.org/lang_corefunc.html
hex(X)The hex() function interprets its argument as a BLOB and returns
a string which is the upper-case hexadecimal rendering of the content
of that blob.
On Sat, Mar 12, 2016 at 12:18 PM, jungle Boogie
wrote:
> Hello All,
>
> I'm curious to know if there's a better way to run a query and convert
> the results to hexadecimal.
>
> This is what I have so far:
> SELECT printf("%x %d",code,code), printf("%x", denom) FROM sidemeters
> order by denom
>
> The data is already in decimal so I don't necessarily need to include
> the extra %d, but it's nice for comparison.
>
>
> Thanks for any input!
>
> --
> ---
> inum: 883510009027723
> sip: jungleboogie at sip2sip.info
> xmpp: jungle-boogie at jit.si
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Minor SQLDIFF issue
Doing SQLDIFF against a non-existent database (e.g., due to a typo) produces a diff which is a series of DROP TABLE ... and also creates an empty file by that name. This is misleading as it looks like a possibly valid diff. Shouldn?t it instead give an error that there is no such database? Thanks.
[sqlite] Creating system table
Igor Korot wrote: > There is a notion of system table vs. user table in the DBMS. System tables are tables with a name beginning with "sqlite_". > My question is: what should I do if I want to create a system table? This is possible only by (ab)using the writable_schema pragma: CREATE TABLE xxx(what, ever, [...]); PRAGMA writable_schema = ON; UPDATE sqlite_master SET name = 'sqlite_xxx', tbl_name = 'sqlite_xxx', sql = replace(sql, 'xxx', 'sqlite_xxx') WHERE name = 'xxx'; Alternatively, modify the SQLite source code. But why would you want to do this? Now you have a table that is not completely accessible, and that will not show up in the output of .dump. Regards, Clemens
[sqlite] Creating system table
On 12 Mar 2016, at 6:07pm, Igor Korot wrote: > There is a notion of system table vs. user table in the DBMS. > > The SQLite has at least 1 system table that is well-known: sqlite_master. There are other tables too: the ones made by ANALYZE which have names which start with 'sqlite_stat' . And there's 'sqlite_user' which is maintained by the user authentication routines. Would you call those "system tables" too ? So I see it as the developers of sqlite reserving table names which start with 'sqlite' for themselves. You should stay away from those names in case your table clashes with something SQLite does internally. > My question is: what should I do if I want to create a system table? What would you want to be special about it because it's a system table ? How are system tables different from user tables ? Simon.
[sqlite] Creating system table
Why all the complication? You just need to set pragma writable_schema=1; so that you can create a table with an ill-advised name (starting with sqlite_). After it is created, it behaves just like any other table -- until you want to drop it -- that requires complication. SQLite version 3.12.0 2016-03-12 16:32:16 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table sqlite_mysystemtable(a); Error: object name reserved for internal use: sqlite_mysystemtable sqlite> pragma writable_schema=1; sqlite> create table sqlite_mysystemtable(a); sqlite> insert into sqlite_mysystemtable values (1); sqlite> pragma writable_schema=0; sqlite> insert into sqlite_mysystemtable values (1); sqlite> insert into sqlite_mysystemtable values (1); sqlite> update sqlite_mysystemtable set a=2 where a=1; sqlite> pragma writable_schema; 0 sqlite> drop table sqlite_mysystemtable; Error: table sqlite_mysystemtable may not be dropped sqlite> pragma writable_schema=1; sqlite> drop table sqlite_mysystemtable; Error: table sqlite_mysystemtable may not be dropped sqlite> > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Clemens Ladisch > Sent: Saturday, 12 March, 2016 11:42 > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] Creating system table > > Igor Korot wrote: > > There is a notion of system table vs. user table in the DBMS. > > System tables are tables with a name beginning with "sqlite_". > > > My question is: what should I do if I want to create a system table? > > This is possible only by (ab)using the writable_schema pragma: > > CREATE TABLE xxx(what, ever, [...]); > PRAGMA writable_schema = ON; > UPDATE sqlite_master >SET name = 'sqlite_xxx', >tbl_name = 'sqlite_xxx', >sql = replace(sql, 'xxx', 'sqlite_xxx') >WHERE name = 'xxx'; > > Alternatively, modify the SQLite source code. > > But why would you want to do this? Now you have a table that is > not completely accessible, and that will not show up in the output > of .dump. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Creating system table
Hi, Clemens, On Sat, Mar 12, 2016 at 1:41 PM, Clemens Ladisch wrote: > Igor Korot wrote: >> There is a notion of system table vs. user table in the DBMS. > > System tables are tables with a name beginning with "sqlite_". > >> My question is: what should I do if I want to create a system table? > > This is possible only by (ab)using the writable_schema pragma: > > CREATE TABLE xxx(what, ever, [...]); > PRAGMA writable_schema = ON; > UPDATE sqlite_master >SET name = 'sqlite_xxx', >tbl_name = 'sqlite_xxx', >sql = replace(sql, 'xxx', 'sqlite_xxx') >WHERE name = 'xxx'; > > Alternatively, modify the SQLite source code. > > But why would you want to do this? Now you have a table that is > not completely accessible, and that will not show up in the output > of .dump. OK, so how many system tables SQLite has? Is there a way to get their info? Thank you. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Creating system table
Hi, Simon, On Sat, Mar 12, 2016 at 1:18 PM, Simon Slavin wrote: > > On 12 Mar 2016, at 6:07pm, Igor Korot wrote: > >> There is a notion of system table vs. user table in the DBMS. >> >> The SQLite has at least 1 system table that is well-known: sqlite_master. > > There are other tables too: the ones made by ANALYZE which have names which > start with 'sqlite_stat' . And there's 'sqlite_user' which is maintained by > the user authentication routines. Would you call those "system tables" too ? > > So I see it as the developers of sqlite reserving table names which start > with 'sqlite' for themselves. You should stay away from those names in case > your table clashes with something SQLite does internally. That's why I wrote "at least". > >> My question is: what should I do if I want to create a system table? > > What would you want to be special about it because it's a system table ? How > are system tables different from user tables ? sqlite> select tbl_name from sqlite_master; leagues owners positions positionsforleague scorehits scorepitch scorehitsforleague scorepitchforleague teams teamsforleague playerposition playerscorehits playerscorepitches targetshits targetspitches playerdraft playerpositioninleague players playersinleague playersinleague leaguescorehitter leaguescorepitcher playerposition leagues playerdraft playerpositioninleague teams playerscorehits playerscorepitches playerpositioninleague leaguescorehitter leaguescorepitcher players players playersinleague abc? I don't sqq here neither sqlite_master, nor sqlite_stat. So is this enough to call the table "sqlite_" to make it a system table? Also, generally speaking, system table is the one that is used by DBMS internally and is not exposed to the user. It can be viewed only with some special tool/means. The best example is sqlite_master: you as a user don't have access to it, unless you have some special tool/means or you know its name. Thank you. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Creating system table
Hi, ALL, There is a notion of system table vs. user table in the DBMS. The SQLite has at least 1 system table that is well-known: sqlite_master. My question is: what should I do if I want to create a system table? Thank you.
[sqlite] hexadecimal conversion on select query
Hello All,
I'm curious to know if there's a better way to run a query and convert
the results to hexadecimal.
This is what I have so far:
SELECT printf("%x %d",code,code), printf("%x", denom) FROM sidemeters
order by denom
The data is already in decimal so I don't necessarily need to include
the extra %d, but it's nice for comparison.
Thanks for any input!
--
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si
