Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Stephan Beal 
On Thu, Jul 24, 2014 at 4:51 PM, Stephan Beal  wrote:

> [stephan@host:~/cvs/fossil/libfossil/src]$ f-query -e "select * from
> ckout.vfile limit 1" -S
>

BTW: the -S option has historically meant "SQL Tracing," but i think i'll
rename it to "Simon" now ;). i've been fighting with this db name juggling
for almost exactly a year, and one line of code resolves it completely.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Stephan Beal 
On Thu, Jul 24, 2014 at 4:45 PM, Simon Slavin  wrote:

>
> On 24 Jul 2014, at 3:38pm, Stephan Beal  wrote:
>
> > THANK YOU!
>
> You're welcome.  I'm still learning more from this list than I'm putting
> out.
>

Hope we never meet, because i will likely kiss you if we do:

[stephan@host:~/cvs/fossil/libfossil/src]$ f-query -e "select * from
ckout.vfile limit 1" -S
SQL TRACE #1: PRAGMA foreign_keys=OFF;
SQL TRACE #2: ATTACH DATABASE '/home/stephan/cvs/fossil/libfossil/_FOSSIL_'
AS ckout;
^^^ that's your baby.

SQL TRACE #3: SELECT value FROM vvar WHERE name='repository';
SQL TRACE #4: ATTACH DATABASE '/home/stephan/cvs/fossil/libfossil.fsl' AS
repo;
SQL TRACE #5: SELECT login FROM user WHERE uid=1;
SQL TRACE #6: SELECT value FROM config WHERE name='allow-symlinks';
SQL TRACE #7: SELECT value FROM vvar WHERE name='checkout';
SQL TRACE #8: SELECT uuid FROM blob WHERE rid=5864;
SQL TRACE #9: BEGIN TRANSACTION;
SQL TRACE #10: select * from ckout.vfile limit 1;
id vid chnged deleted isexe islink rid mrid mtime pathname origname
1397 5864 0 0 0 0 2605 2605 1395763875 .fossil-settings/binary-glob NULL
SQL TRACE #11: COMMIT;
SQL TRACE #12: DETACH DATABASE repo;

before your patch, that would have failed with "unknown db" because ckout
was only known as "main".

So

[stephan@host:~/cvs/fossil/libfossil/src]$ f com -m "Eureka: Simon Slavin
found a way to apply a concrete name to the main db. Seems to work."
fsl_cx.c
Autosync:
http://step...@fossil.wanderinghorse.net/repos/libfossil/index.cgi
Round-trips: 1   Artifacts sent: 0  received: 0
Pull finished with 2964 bytes sent, 2238 bytes received
New_Version: 5abda43115e11c357aa36a1b7231780767b04c23
Autosync:
http://step...@fossil.wanderinghorse.net/repos/libfossil/index.cgi
Round-trips: 1   Artifacts sent: 2  received: 0
Sync finished with 5625 bytes sent, 4906 bytes received

Resulting in:

http://fossil.wanderinghorse.net/repos/libfossil/index.cgi/info/5abda43115e11c357aa36a1b7231780767b04c23


THANK YOU!

(But i still think the ability to rename the main would be a useful
feature!)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Simon Slavin 

On 24 Jul 2014, at 3:38pm, Stephan Beal  wrote:

> THANK YOU!

You're welcome.  I'm still learning more from this list than I'm putting out.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Stephan Beal 
On Thu, Jul 24, 2014 at 4:10 PM, Simon Slavin  wrote:

>
> On 24 Jul 2014, at 3:07pm, Stephan Beal  wrote:
>
> > A simpler solution which would serve my goals just as well: the ability
> to
> > rename only 'main' (e.g. sqlite3_rename_db(sqlite3*, char const *
> > newName)). i don't need 'main' because main is fluid in these apps. i
> need
> > a well-defined name which sticks with a db regardless of whether it is
> > opened or attached.
>
> What happens if you open any SQLite database, then ATTACH the same
> database to the same connection ?
>
>
i was almost ready to kiss you, but that seems to not work:

[stephan@host:~/cvs/fossil/libfossil/s2]$ sqlite3 foo.db
SQLite version 3.8.4.1 2014-03-12 19:38:38
Enter ".help" for usage hints.
sqlite> create table t(a);
sqlite> attach 'foo.db' as foo;
sqlite> .schema foo.t
sqlite> .dump foo.t
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> .schema t
CREATE TABLE t(a);
sqlite>

And yet...

sqlite> insert into foo.t(a) values(1),(2),(3);
sqlite> select * from foo.t;
1
2
3
sqlite> ^D
[stephan@host:~/cvs/fossil/libfossil/s2]$ sqlite3 foo.db
SQLite version 3.8.4.1 2014-03-12 19:38:38
Enter ".help" for usage hints.
sqlite> select * from t;
1
2
3

So ... that seems to work (just not with those shell .commands, but that's
okay). i'll try it out and come back crying if it doesn't.

THANK YOU!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Simon Slavin 

On 24 Jul 2014, at 3:07pm, Stephan Beal  wrote:

> A simpler solution which would serve my goals just as well: the ability to
> rename only 'main' (e.g. sqlite3_rename_db(sqlite3*, char const *
> newName)). i don't need 'main' because main is fluid in these apps. i need
> a well-defined name which sticks with a db regardless of whether it is
> opened or attached.

What happens if you open any SQLite database, then ATTACH the same database to 
the same connection ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Stephan Beal 
On Thu, Jul 24, 2014 at 4:02 PM, Simon Slavin  wrote:

> Really ?  It would dramatically simplify your programming and not take up
> much space.  Oh well.
>

It's not the space, but the "pile of files" debate which has raged for
years in SCMs. Fossil already has its one "clutter" file, just like svn/git
have their .svn/.git dirs. libfossil (a library-based implementation of
Fossil) can't justify adding its own clutter to that.

A simpler solution which would serve my goals just as well: the ability to
rename only 'main' (e.g. sqlite3_rename_db(sqlite3*, char const *
newName)). i don't need 'main' because main is fluid in these apps. i need
a well-defined name which sticks with a db regardless of whether it is
opened or attached.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Simon Slavin 

On 24 Jul 2014, at 2:52pm, Stephan Beal  wrote:

> note that i can't justify using a file for this purpose, because that file
> has to live somewhere, and the only reasonable place for it is in the
> checkout directory. It would clutter the source trees.

Really ?  It would dramatically simplify your programming and not take up much 
space.  Oh well.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Stephan Beal 
On Thu, Jul 24, 2014 at 3:51 PM, Stephan Beal  wrote:

> i did in fact try that (way back in the beginning), using a :memory: db as
> my main db.
>

note that i can't justify using a file for this purpose, because that file
has to live somewhere, and the only reasonable place for it is in the
checkout directory. It would clutter the source trees.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Stephan Beal 
On Thu, Jul 24, 2014 at 3:18 PM, Simon Slavin  wrote:

> Create a fourth database with no content.  That's always the main one.
>  Everything else is always attached to it.
>

i did in fact try that (way back in the beginning), using a :memory: db as
my main db. However, the :memory: VFS is (interestingly) not capable of
generating temp file names, and i need that feature :/.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Simon Slavin 

On 24 Jul 2014, at 2:11pm, Stephan Beal  wrote:

> The problem is, an application does not
> (cannot) necessarily know which order the dbs were opened, so it doesn't
> really know if "main" is the repo db, the checkout db, or the config db.

Create a fourth database with no content.  That's always the main one.  
Everything else is always attached to it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] feature request: aliasing (or renaming) db (not table) names

2014-07-24 Thread Stephan Beal 
Hi, sqlite team,

i meant to address this directly to Richard a couple weeks back, but we got
carried away with other topics...

In the Fossil SCM and (by extension) libfossil, we juggle 1-3 db handles
for the config, checkout, and repo dbs. The first db which gets opened
(it's use-case dependent!) becomes the "main" db and the others get
attached with a well-defined name. The problem is, an application does not
(cannot) necessarily know which order the dbs were opened, so it doesn't
really know if "main" is the repo db, the checkout db, or the config db.

Normally this is not a problem because we have no table name collisions
which would require full qualification. However, it becomes exceedingly
tedious when, for example, a client app wants to install a new table in
(e.g.) the repo db. He doesn't know if he needs to use "create table
main.foo..." or "... repo.foo...". He can, thanks to C APIs, fetch this
info, but it intrudes quite a lot on the client code. (@Richard: this
hasn't come up in fossil(1) yet only because client's don't have a way to
do it, but it has come up in fossil(3) while experimenting with
client-installed extensions.)

My request is the ability to alias or rename the db (not table) names, so
that i can tell libfossil that, e.g., "repo" is always the repo db, even if
it's really (also) "main".

Perhaps a pragma:

pragma table_alias oldname aliasname
or
pragma table_rename oldname newname

either one would suit my purposes just fine.

:-?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users