Re: [sqlite] Restrictions on table names

[miguel manese]

There is almost no restrictions, just put it inside square brackets

create table foobar ([my $0.02 column] int, ...)

M. Manese

On 2/20/07, Pablo Santacruz <[EMAIL PROTECTED]> wrote:

I'd like to know if there is any restriction on table names.

Thanks in advance

--
Pablo



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] about default file permission of SQLite database file

[miguel manese]

This is not actually about SQLite. man umask

M. Manese

On 2/22/07, Shan, Zhe (Jay) <[EMAIL PROTECTED]> wrote:

Hi,

If to use SQLite to create a database in Linux, the database file will
be granted permission 644 as default.
Is this value hardcoded in the current version? Is it possible to
change this default vaule, say to 664 or something else?


Thanks.

Jay



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using AVG() Correctly

[miguel manese]

On 2/22/07, Rich Shepard <[EMAIL PROTECTED]> wrote:

   However, I'm stuck on the proper SQL syntax. A nudge in the right
direction -- including pointers to the appropriate documentation -- would be
much appreciated.

The "rule of thumb" is that anything that appears in the group-by
clause can appear directly in the select, while other columns are
aggregated in the select. Best example using your particular case is
Igor's answer:

select cat, pos, avg(col1), avg(col2), ...
from voting
group by cat, pos;

M. Manese

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Newbee Question

[miguel manese]

You have to link against sqlite's shared lib, e.g. in linux

$ gcc -L/path/to/sqlite/stuffs -I/path/to/sqlite/stuffs -lsqlite prog.c

(the 2nd is a capital i, the 3rd a small L)

Cheers,
M. Manese

On 4/4/07, nshaw <[EMAIL PROTECTED]> wrote:


I upgraded from 3.3.1.3 to 3.3.1.4.  Up till now, I've been experimenting
with SQLite3 via the CLP.  Now, I'm trying to access a DB via a C program
but I'm getting errors.  Here's a small code fragment:

#include 
#include 
#include "sqlite3.h"

int main (int argc, char **argv)
{
sqlite3 *db; /* from "the definitive guide to sqlite */

char *zErr;
int rc;
char *sql;

rc = sqlite3_open ("Family.db", &db);

return 0;
}

The error I get is (.text+0x16): undefined reference to 'sqlite3_open'.

Any help will be greatly appreciated.
Thanks,

Nick.

--
View this message in context: 
http://www.nabble.com/Newbee-Question-tf3521982.html#a9825045
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Longest "real" SQL statement

[miguel manese]

I am the author of the package SQLiteDF for R (a statistical package),
some sort of sqlite backed "data set". It's "raison d'etre" is to deal
with very large datasets, which could be tables with thousands of
columns. I am not much on the infinite length sql statement, but I
need lots of columns in the result. I plan to hack my way into
extending the syntax to something like

select col1 ... col100 from table

which is shorthand for select col1,col2,col3,..., col100. So a
result set with lots of columns would be very nice.

Thanks,
M. Manese


On 5/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

I'm looking for an upper bound on how big legitimate
SQL statements handed to SQLite get to be.  I'm not
interested in contrived examples.  I want to see
really big SQL statements that are actually used in
real programs.

"Big" can be defined in several ways:

*  Number of bytes of text in the SQL statement.
*  Number of tokens in the SQL statement
*  Number of result columns in a SELECT
*  Number of terms in an expression

If you are using really big SQL statements, please
tell me about them.  I'd like to see the actual
SQL text if possible.  But if your use is proprietary,
please at least tell me how big your query is in
bytes or tokens or columns or expression terms.

Thanks.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser

[miguel manese]

Maybe you really have to loop "outside" sqlite to align the rows &
values. From the result you got and the UPDATE documentation, I can
guess that the subselect in the assignment is flattened to a scalar.
Unfortunately sqlite does not have something like

update tbl1 set col=tbl2.col from tbl2 where tbl2.id=tbl1.id

which would have been what you needed.

Cheers,
M. Manese

On 6/8/07, Ellis Robin (Bundaberg) <[EMAIL PROTECTED]> wrote:

Thanks John, that's what I thought too... My positioning on a row is
dependent on 2 values, and without any better knowledge of SQLite I've
tried implementing this:

Update
  Parameter
set
ParameterValue = (select NewParams.parametervalue from Scenario, Link,
Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU,
Parameter, NewParams where Scenario.ScenarioID = 1004 and
Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID
and FunctionalUnit.CatchmentID = Catchment.CatchmentID and
FunctionalUnit.FunctionalUnitDefinitionID =
FunctionalUnitDefinition.FunctionalUnitDefinitionID and
FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and
StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name
= NewParams.Subcatchments and FunctionalUnitDefinition.Name =
NewParams.FU_name and Parameter.Parameter = NewParams.parameter)

Where
Parameter.ModelID = (select Parameter.ModelID from Scenario, Link,
Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU,
Parameter, NewParams where Scenario.ScenarioID = 1004 and
Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID
and FunctionalUnit.CatchmentID = Catchment.CatchmentID and
FunctionalUnit.FunctionalUnitDefinitionID =
FunctionalUnitDefinition.FunctionalUnitDefinitionID and
FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and
StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name
= NewParams.Subcatchments and FunctionalUnitDefinition.Name =
NewParams.FU_name and Parameter.Parameter = NewParams.parameter)

and
Parameter.Parameter = (select Parameter.Parameter from Scenario, Link,
Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU,
Parameter, NewParams where Scenario.ScenarioID = 1004 and
Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID
and FunctionalUnit.CatchmentID = Catchment.CatchmentID and
FunctionalUnit.FunctionalUnitDefinitionID =
FunctionalUnitDefinition.FunctionalUnitDefinitionID and
FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and
StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name
= NewParams.Subcatchments and FunctionalUnitDefinition.Name =
NewParams.FU_name and Parameter.Parameter = NewParams.parameter)

However my results are the same with this attempt at row positioning.
Both of the select statements used in the WHERE expression do retturn
valid records. Any more hints?

Rob



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Saving an in-memory database to file

[miguel manese]

Or, attach then INSERT-SELECT

On 7/25/07, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote:

How about dumping and import into new db?


- Original Message -
From: "Colin Manning" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, July 25, 2007 7:05 AM
Subject: [sqlite] Saving an in-memory database to file


> Hi
>
> If I create an in-memory database (by opening with ":memory:"), then add
> tables etc to it, is it possible to then write it to a disk file so it can
> subsequently be used as a file-based db?
>
> Thanks
>
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG. Version: 7.5.476 / Virus Database: 269.10.14/912 - Release
> Date: 22/07/2007 19:02
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: auto_increment - why does it do the opposite?

[miguel manese]

Igor has answered this before. Roughly:

1. all tables has an implicit integer column named "rowid" that is
auto increment
2. creating an integer primary key effectively "renames" rowid to that
column, so in your case below fields id and rowid are the same

IIRC drh replied something else, and since I'm not familiar with the
internals I'm not sure if that is how it is implemented or an
equivalent black-box model of how sqlite works. But since Igor is like
the next authoritative guru here after drh himself, that explanation
should be correct.

Cheers,
M. Manese

On 8/9/07, Paul Harris <[EMAIL PROTECTED]> wrote:
> I dislike answering myself, but no sooner I had sent that email, I tried this:
>
> sqlite> create table ghi (id integer primary key autoincrement, value text);
> sqlite> insert into ghi (value) values ('justvalue');
> sqlite> insert into ghi (id,value) values (null,'withid');
> sqlite> select * from ghi;
> id|value
> 1|justvalue
> 2|withid
> sqlite>
>
> so, why doesn't auto_increment do either of the following more sensible 
> things:
> 1) throw an error
> 2) do what "autoincrement" does
>
> ?
>
> thanks
> Paul
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] any way to SELECT the 100th-104th row of a table?

[miguel manese]

select * from table limit (n-1),(m-n)

n-1 because it is 0-based

http://www.sqlite.org/lang_select.html

On 10/8/07, Adam Megacz <[EMAIL PROTECTED]> wrote:
>
> Hello.  This is probably a stupid question, but...
>
> Is there any way to include some phrase in a SELECT clause that will
> match only the Nth-Mth rows of a table, for some values of N and M?
>
> Note that ROWID isn't what I'm looking for -- if you delete rows from
> a table the ROWID no longer matches the "row number".
>
> Thanks,
>
>   - a
>
> --
> PGP/GPG: 5C9F F366 C9CF 2145 E770  B1B8 EFB1 462D A146 C380
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: How do I do this

[miguel manese]

On Jan 14, 2008 9:09 AM, Vishal Mailinglist <[EMAIL PROTECTED]> wrote:
> > sno | id | amount
> > > 1| 1 |  200
> > > 2| 1 | 300
> > > 3   |  2 | 100
> > > 4  | 2 | 100
> > > 5 | 1 | 500
> What if I do not have control over sno i.e it is  random or unpredictable ,
> I want to subtract it in order of occurrence. Like doing subtracion of sno 2
> and 5 and so on may be next occurance for id 1 is at sno 20 , then what.

The simple solution is to select both rows separately and then
subtract it in your application. Really, if it were me I'd do that.

Or you can use sum(), the only problem is how to make the the amount
to be subtracted negative. Something like below, where the amount with
the lower sno is turned negative.

select sum(case when sno=(select min(sno) from tableName where (sno=?
and id=?) or (sno=? and id=?)) then -amount else amount end)
from tableName where (sno=? and id=?) or (sno=? and id=?)

M. Manese

-
To unsubscribe, send email to [EMAIL PROTECTED]
-