Re: [sqlite] Make a database read-only?

2014-10-14 Thread John Hascall
Some code you may find useful to enforce the readonly byte (do this before
your program opens the DB).
John

#include 
#include 
#define

int setRObyte (
const char * sqDBfn
) {
int fd  = open(sqDBfn, O_WRONLY, 0);
int rc  = -1;

if (fd == -1) return -1;
#ifdef  HAVE_PWRITE
if (pwrite(fd, "\143", (size_t)1, (off_t)18) == 1) rc = 0;
#else
if (lseek(fd, (off_t)18, SEEK_SET) == (off_t)18) == 1) ? 0 : -1;
rc = (write(fd, "\143", (size_t)1) == 1) ? 0 : -1;
} else rc = -1;
#endif
(void)close(fd);
return rc;
}

On Tue, Oct 14, 2014 at 7:23 AM, Richard Hipp  wrote:

> On Tue, Oct 14, 2014 at 2:19 AM, Ross Altman 
> wrote:
>
> > I need to host a fixed, unchanging database online, and I want to make
> sure
> > that anyone who downloads it cannot add to it. Is there any way to set
> the
> > permissions to be read-only within sqlite?
> >
>
> Change the 18th byte of the file from 1 or 2 to 99.
>
> Anyone who downloads the file can always change that byte back to its
> original value using a binary editor and then write the database.  But you
> have at least then made the problem more difficult for them.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Make a database read-only?

2014-10-14 Thread John Hascall
Well some keystroke I hit apparently made the stupid browser send that
before I finished editing it, but you get the idea


Sigh,
John

On Tue, Oct 14, 2014 at 9:12 AM, John Hascall <j...@iastate.edu> wrote:

> Some code you may find useful to enforce the readonly byte (do this before
> your program opens the DB).
> John
>
> #include 
> #include 
> #define
>
> int setRObyte (
> const char * sqDBfn
> ) {
> int fd  = open(sqDBfn, O_WRONLY, 0);
> int rc  = -1;
>
> if (fd == -1) return -1;
> #ifdef  HAVE_PWRITE
> if (pwrite(fd, "\143", (size_t)1, (off_t)18) == 1) rc = 0;
> #else
> if (lseek(fd, (off_t)18, SEEK_SET) == (off_t)18) == 1) ? 0 : -1;
> rc = (write(fd, "\143", (size_t)1) == 1) ? 0 : -1;
> } else rc = -1;
> #endif
> (void)close(fd);
> return rc;
> }
>
> On Tue, Oct 14, 2014 at 7:23 AM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Tue, Oct 14, 2014 at 2:19 AM, Ross Altman <altman...@husky.neu.edu>
>> wrote:
>>
>> > I need to host a fixed, unchanging database online, and I want to make
>> sure
>> > that anyone who downloads it cannot add to it. Is there any way to set
>> the
>> > permissions to be read-only within sqlite?
>> >
>>
>> Change the 18th byte of the file from 1 or 2 to 99.
>>
>> Anyone who downloads the file can always change that byte back to its
>> original value using a binary editor and then write the database.  But you
>> have at least then made the problem more difficult for them.
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Why is a b-tree sort required for this query?

2014-11-17 Thread John Hascall
​If you do
   ORDER BY t1c.t1_id, t1c.id;

then you won't have the b-tree step, but ​including the name fields means
it has the extra work to do to satisfy your order by.  Or am I missing
something?

John



On Sun, Nov 16, 2014 at 1:18 PM, Oliver Smith  wrote:

> In the following scenario:
>
>CREATE TABLE t1 (id INTEGER, name text, UNIQUE (name));
>CREATE TABLE t1c (id INTEGER, name text, t1_id INTEGER, UNIQUE (name));
>CREATE INDEX idx_t1c_by_t1_id ON t1c (t1_id, id);
>
>CREATE TABLE t2 (id INTEGER, name text, UNIQUE(name));
>CREATE TABLE t2c (id INTEGER, name text, t2_id INTEGER, UNIQUE
>(t2_id, name));
>
> I have a query designed to generate a row for t2c ordered by t2 for every
> instance of t1c ordered by t1 id and then t1c id.
>
> The query uses indexes and those should ensure that the results are in the
> order I am specifying:
>
>EXPLAIN QUERY PLAN
>SELECT t1c.t1_id, t1c.id, t2c.t2_id, t2c.id
>FROM t1c,
>  t2 INNER JOIN t2c ON (t2c.t2_id = t2.id)
>ORDER BY t1c.t1_id, t1c.id, t2.name, t2c.name
>;
>
> And yet the plan invokes a B-Tree to sort:
>
>"0""0""0""SCAN TABLE t1c USING COVERING INDEX
>idx_t1c_by_t1_id"
>"0""1""2""SCAN TABLE t2c"
>"0""2""1""SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX
>(id=?)"
>"0""0""0""USE TEMP B-TREE FOR RIGHT PART OF ORDER BY"
>
> Is the temp b-tree redundant here?
>
> $ sqlite3 --version
> 3.8.5 2014-06-04 14:06:34 b1ed4f2a34ba66c29b130f8d13e9092758019212
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Insert Hindi Language

2014-11-18 Thread John Hascall
See also http://www.sqlite.org/pragma.html#pragma_encoding
John

On Tue, Nov 18, 2014 at 4:42 AM, ARVIND KUMAR 
wrote:

> Hi,
>
> I am using SQLite version 3.8.7.1
>
> I am not able to insert Hindi Language into table. So please help me
> regarding this.
>
> May you send me the table structure and insert query for inserting Hindi
> text into table.
>
>
> Thanks & Regards
> Arvind
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] An order by problem, maybe a bug?

2014-09-20 Thread John Hascall
The problem with your suggestion of 'two uses => two fields' is that no sooner 
do you do that then somebody comes up with additional uses, for example, formal 
greeting, informal greeting, the appropriate form for government form X123, and 
so on....

John Hascall
IT Services
Iowa State Univ.

> On Sep 20, 2014, at 2:21 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> 
> 
>> On 20 Sep 2014, at 7:42pm, James K. Lowden <jklow...@schemamania.org> wrote:
>> 
>> I'm saying more than one sort order is often needed.  If you don't
>> distinguish among the components of the person's name, you can't sort
>> by those components.  
> 
> I don't understand why anyone would want to sort on firstname or middle name. 
>  I suppose having them indexed might be useful for searching, but apart from 
> very specific census questions like "How popular were certain names in the 
> 1990s ?" I see no reason to do it.
> 
>> You offered the OP sweeping contrarian advise on how to represent names
>> in a database, referring him to one odd source having nothing to do
>> with databases.
> 
> That one odd source is so widely quoted nobody else has bothered to write 
> anything on the subject.  A search on "People have exactly one canonical full 
> name" gives me 1,830 hits and that's just the quotes rather than the links 
> and references.
> 
>> Your suggestion essentially amounts to "names are not
>> decomposable, so keep one version for the user and one for the
>> system."
> 
> Sorry, I don't think I got that across effectively.  If I make up a database 
> that stores names, I'm storing them pretty-much for two main reasons: to know 
> the name and to sort by name on displays and printouts.  So I keep one column 
> for the whole name ("to know the name") and one for the name in the order I 
> want to see it sorted ("to sort by names").  Two things you need, make two 
> columns.  I see no reason to split name up into three artificial units.
> 
> Searching by name is going to require LIKE and Soundex or something like it.  
> I've seen a database where a third column was used to store the soundex 
> encoding of the full name.  (Might have been something else like Soundex.  It 
> was a long time ago.)  In SQLite you could probably feed that column with 
> TRIGGERs.
> 
>> Given the thousands of databases out there that use first,
>> middle, and last names as columns -- with apparent success -- and
>> theoretical and practical problems arising from your alternative, I
>> suggest your advice is ill-founded.  
> 
> I suspect that those thousands of databases had their fields defined in the 
> 1970s, along with checking to see that all three fields weren't blank and 
> didn't have anything but letters in them.  And that such things would only be 
> done in the US.  Certainly anyone who designed a database that way in England 
> (where I live) would have to immediately write a page full of instructions 
> about how to squeeze many names I see around here into those three fields.
> 
> If you want to assess their success, ask yourself how many times you've seen 
> NO-MIDDLE-NAME in official records.  Or someone who apparently has the 
> lastname of OMALLEY.  Or someone who apparently has a lastname of Al-Haj and 
> is therefore sorted into the 'A's.
> 
> Anyone who worked for a big company these days and created such a database 
> should get called in and told to do it again properly.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Results of Joins in sqlite

2014-09-24 Thread John Hascall
This is a really terrible idea.  It is dependent on the internals of sqlite
which makes it extremely fragile.
Have you profiled your code to show that this is your bottleneck?  If so,
it's still a terrible idea,
but at least has some basis for considering the idea.

John

On Wed, Sep 24, 2014 at 8:06 AM, Prakash Premkumar 
wrote:

> Thanks a lot , Simon and Hick,
>
> What I am looking for is , instead of iterating through the result which
> sqlite provides and then form the respective objects and setting pointers,
> is it possible to hack sqlite to fill in the objects this way.
> I would like to prevent the extra iteration through the result set.
>
> When the result row is formed in OP_ResultRow, we should be able to form
> the structs and their links. Any pointers in this direction?
>
> Thanks
> Prakash
>
> On Wed, Sep 24, 2014 at 5:36 PM, Hick Gunter  wrote:
>
> > How about writing something that uses the SQLite Api as intended and
> works
> > first?
> >
> > -Ursprüngliche Nachricht-
> > Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> > Gesendet: Mittwoch, 24. September 2014 12:54
> > An: General Discussion of SQLite Database
> > Betreff: Re: [sqlite] Results of Joins in sqlite
> >
> > Thanks a lot Hick,for your approach.
> > With the approach you suggested, we are creating extra queries and if the
> > join is on n tables there will be n+1 queries , and each query will have
> to
> > go through a query planning stage.
> > Is there an alternative idea ?
> >
> > Thanks a lot
> > Prakash
> >
> > On Wed, Sep 24, 2014 at 3:34 PM, Hick Gunter  wrote:
> >
> > > You are free to build your own result conversion routine on top of the
> > > SQLite Api.
> > >
> > >
> > > May I suggest selecting the rowids of the tables too i.e.
> > >
> > > SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM
> > > <...your join...>;
> > >
> > > When you first come across a new rowid you can create your memory
> > > object and populate it from the required fields.
> > > The disadvantage ist hat SQLite will still retrieve all requested
> > > fields for each result row.
> > >
> > >
> > > Or maybe you might like to
> > >
> > > SELECT t1.rowid, t2.rowid, t3.rowid FROM <...your join...>;
> > >
> > > and then populate your structure by binding the rowid received and
> > > executing the appropriate
> > >
> > > SELECT <...t1 fields> FROM t1 where t1.rowid=?; SELECT <...t2 fields>
> > > FROM t2 where t2.rowid=?; SELECT <...t3 fields> FROM t3 where
> > > t3.rowid=?;
> > >
> > >
> > >
> > > -Ursprüngliche Nachricht-
> > > Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> > > Gesendet: Mittwoch, 24. September 2014 09:32
> > > An: General Discussion of SQLite Database
> > > Betreff: Re: [sqlite] Results of Joins in sqlite
> > >
> > > Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8
> columns.
> > >
> > > When one result row comes in , I want to create an object for each
> > > table (My application map one struct to one table) and put the columns
> > > of respective tables in their individual structs. i,e  I would set
> > > column 0 and column 1 in result row to the struct of T1 and column 2
> > > and 3 to that of T2 and so on.
> > >
> > > and I set pointers between T1 and T2 , T2 and T3.
> > > When a new row comes in and if the object for that row is already
> > created.
> > > i.e if r11 again comes as output, I will not create a new
> > > object,instead I would use the old object and set pointers between the
> > > old T1 and object and the T2 object (if r21 is new,else do not set
> > > pointers and do not create
> > > objects)
> > >
> > > similarly for T3. Thus,for the above case (the example we discussed),
> > > there will be one pointer from T1 row to T2 row and 3 pointers from T2
> > > row to the
> > > 3 T3 rows(one pointer per row).
> > >
> > > The end data structure will look like a graph.
> > >
> > > I want the API to return this graph./ is there an alternative ?
> > > API call : graph* sqlite3_join(select_stmt*)
> > >
> > > Thanks
> > > Prakash
> > >
> > >
> > >
> > >
> > > On Wed, Sep 24, 2014 at 12:45 PM, Clemens Ladisch 
> > > wrote:
> > >
> > > > Prakash Premkumar wrote:
> > > > > Let's say I have tables T1,T2 and T3 with 2 columns each and I am
> > > > joining them.
> > > > > The result rows will have 8 columns each.
> > > >
> > > > No.  The result will have between 4 and 6 columns, depending on how
> > > > you do the joins.
> > > >
> > > > Example:
> > > >
> > > > CREATE TABLE T1(ID1, Name);
> > > > INSERT INTO "T1" VALUES('r11',NULL); CREATE TABLE T2(ID2, ID1);
> > > > INSERT INTO "T2" VALUES('r21','r11'); CREATE TABLE T3(ID3, ID2);
> > > > INSERT INTO "T3" VALUES('r31','r21'); INSERT INTO "T3"
> > > > VALUES('r32','r21'); INSERT INTO "T3" VALUES('r33','r21');
> > > >
> > > > SELECT * FROM T1 NATURAL JOIN T2 NATURAL JOIN T3;
> > > >
> > > > ID1 NameID2 ID3
> > > > 

Re: [sqlite] Division accuracy

2014-09-24 Thread John Hascall
Is the approach of 'just try it and if it goes badly fix it' doable?

mid = (lo + hi) / 2;
if ((mid <= lo) || (mid >= hi)) {
Fix it
}



John Hascall
IT Services
Iowa State Univ.

> On Sep 24, 2014, at 11:49 AM, RSmith <rsm...@rsweb.co.za> wrote:
> 
> I'm trying to find what the limit is for dividing in terms of accuracy.
> 
> Basically I have one program that inserts values to a table and determine 
> sort order using one standard trick that has a REAL column named "SortOrder" 
> which gets the value Highest_previous_value+1 if an insert happens with 
> something that needs to be sorted at the end of the table.
> 
> For any other position, the SortOrder gets assigned the value: 
> ((Prev.Sortorder + Next.Sortorder) / 2)
> 
> So to be clear (in case anyone is not familiar with this method), let's start 
> with a small table with 1 item added like this:
> 
> ID | SortOrder | Data
> 1  |   1 | 'First Row'
> 
> Adding two new rows to the end every time will use previous highest 
> SortOrder+1 so that the result is:
> 
> ID | SortOrder | Data
> 1  |   1 | 'First Row'
> 2  |   2 | 'Eventual Fourth Row'
> 3  |   3 | 'Last Row'
> 
> Adding a new row that should Sort in between IDs 1 and 2 above will take 
> those SortOrders and find a new Order value by dividing the total for IDs 1 
> and 2 (=3) by 2 (=1.5):
> 
> ID | SortOrder | Data
> 1  |   1 | 'First Row'
> 2  |   2 | 'Eventual Fourth Row'
> 3  |   3 | 'Last Row'
> 4  | 1.5   | 'New Second Row'
> 
> Adding another row that should Sort in between IDs 2 and 4 will again total 
> and divide by 2 (=(2+1.5)/2):
> 
> ID | SortOrder | Data
> 1  |   1 | 'First Row'
> 2  |   2 | 'Eventual Fourth Row'
> 3  |   3 | 'Last Row'
> 4  | 1.5   | 'New Second Row'
> 5  | 1.75| 'New Third Row'
> 
> So that if the Query 'SELECT Data FROM t ORDER BY SortOrder' executes it goes 
> like this:
> 
> Data
> 'First Row'
> 'New Second Row'
> 'New Third Row'
> 'Eventual Fourth Row'
> 'Last Row'
> 
> 
> This seems like a clever method and I've seen it used a few times, but it 
> really can break easily if you keep dividing by two, there is a very quick 
> limit in accuracy where one value can no longer be divided by two 
> meanigfully. In 64-bit Floating point Math that limit is very far away, quite 
> a few iterations (assuming normal floating point mantissa accuracy - the 
> exponent size does not matter since any two such values will be adjacent in 
> the same realm of magnitude and only the available real numbers in between 
> them counts), but if inserts happen 2 to 3 times a second, and imagining for 
> a moment that the sort might hit the same spot every time, many consecutive 
> divs might be exhausted quick.
> 
> The question is - how can I accurately establish how many 
> total-then-divide-by-2's a set of co-values in 64-bit FP guise can withstand 
> before the difference is too small to make sense to the sorter in SQLite?
> 
> Reason: The fix is easy but costly on a large DB, sort and reassign 
> SortOrders simply in Integer steps: 1, 2, 3 etc., but I want to establish how 
> often this should be done, as little as possible preferred, but not so little 
> as to allow the order to break or div0 errors or such.
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

2014-09-25 Thread John Hascall
Would dropping the non-functioning default clause from the schema be a
"breaking change"?

That is from:
# sqlite3 dummy.db
sqlite> CREATE TABLE x(
   ...> id INTEGER PRIMARY KEY DEFAULT (random()),
   ...> val VARCHAR
   ...> );
sqlite> .schema
CREATE TABLE x(
*id INTEGER PRIMARY KEY DEFAULT (random()),*
val VARCHAR
);
to:
# sqlite3 dummy.db
sqlite> CREATE TABLE x(
   ...> id INTEGER PRIMARY KEY DEFAULT (random()),
   ...> val VARCHAR
   ...> );
sqlite> .schema
CREATE TABLE x(
*id INTEGER PRIMARY KEY*,
val VARCHAR
);

And would it be better, or cause more head-scratching, I dunno...


John

On Thu, Sep 25, 2014 at 4:07 PM, Richard Hipp  wrote:

> On Thu, Sep 25, 2014 at 4:46 PM, Mark Lawrence  wrote:
>
> >
> > If you are going to keep this behaviour would it not make more sense to
> > ensure that the table creation fails? The DEFAULT clause is pretty
> > straight-forward and I don't find it intuitive to go looking for
> > PRIMARY KEY documentation when it is ignored.
> >
> > SQLite should either fail to accept the statement or do what the table
> > definition says - anything else means heartache for those debugging
> > problems (which I did for hours on this issue) and those reading the
> > code afterwards.
> >
>
> That would break backwards compatibility for the millions and millions of
> applications currently using SQLite.  Most of those millions would be
> unaffected, no doubt, but out of millions I'm sure there are a goodly
> number that would break.  I am unwilling to implement a breaking change
> simply to make the interface more "intuitive".
>
> Had you brought this up in 2002, the outcome would likely have been very
> different.  But at this point, the behavior of INTEGER PRIMARY KEY in
> SQLite is not something that can be modified.
>
> Sorry to disappoint.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] CLI dump command ignores view dependencies

2014-10-01 Thread John Hascall
I think his point was:

illegal to create a view referring to a non-existing table
AND
illegal to later create a situation where you have a view referring to a
non-existing table
is logical

legal to create a view referring to a non-existing table
AND
legal to later create a situation where you have a view referring to a
non-existing table
is logical
​
But, the existing situation where the first is​
​ illegal but the second is legal has
a certain illogic.


John​


On Wed, Oct 1, 2014 at 9:05 AM, Stephan Beal  wrote:

> On Wed, Oct 1, 2014 at 3:53 PM,  wrote:
>
> > If it indeed does matter, then shouldn’t dropping view a (in the above
> > example) also drop view b, automatically?
> >
>
> That assumes view 'a' somehow knows that it is the only consumer of 'b',
> which it cannot know. Views from other db files, possibly not attached,
> might be consumers of 'b'.
>
>
> --
> - 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] calculating in the command line interface

2014-10-01 Thread John Hascall
​You got bit by integer division...

asw-1# {773} *sqlite3*
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> *select round(133.0/122000.0,10) as t;*
0.0010901639
sqlite>


BTW, there are other tools for simple math...​

asw-1# {772} *bc -l*

*scale=10133/122000*
.0010901639


On Wed, Oct 1, 2014 at 9:27 AM, jose isaias cabrera  wrote:

>
> So, I needed to get a really low percentage and I went to the SQlite3
> command prompt and I typed,
>
> select  round(133/122000,10) as t;
>
> that gave me 0.0.  Then, I said, ok, let's try this,
>
> select round(1/2,10) as t;
>
> that also gave me 0.0.  Then I said, h, let me try this,
>
> select round(10/2,10) and that gave me 5.0, which is what I expect.  Is
> there a way that I can calculate some fractional numbers in the sqlite3
> command line?  I am using v3.8.6.  Thanks.
>
> josé
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users