Re: [sqlite] VACUUM function problem
Hello All,
Recently, I have installed sqlite3 and trying to get a very simple
database application working with it. My application is in C++. I am
trying to use sqllite3_exec and sqlite3_get_table etc to insert records
into my database. I have already created a table and database pointer is
already pointing to the opened database. Now I have following query to
execute:
my query string is:
insert into sdb
(id,resclass,type,manufacturer,method,ownership,precision,minrange,maxrange,capacity)
values (43,'HARDWARE','MONITOR','GE','NA','XYZ',0,0,4,2)
Same string on commandline works just fine. I think it might be related to
string variables and escape charasters. So I tried using the following:
sqlite3_exec(db,query, NULL, 0, );
I was getting unrecognized token: ""
I also tried doing this: this
char* tpquery;
tpquery = sqlite3_mprintf("%q",query);
Now I get SQL error near "HARDWARE": syntax error
Can someone please give me some hint.
Regards,
--Sameer.
On Tue, 28 Dec 2004, D.W. wrote:
> Thank you very much to all who replied to me. I misunderstood the vacuum
> function.
> I had problems with my output routine because of the empty spaces. I thought
> to solve the problem that way.
> I have just solved the problem by using another output routine.
> Regards Daniel
>
>
> > > I have deleted some objects in my database. Now I have objects at id
> > > (INTEGER PRIMARY KEY) =1,3,4,5,7,8,9,16,17,20 .
> > > id=2,6,10,.. are empty. I want to defrag the database so that I have
> > objects
> > > continuously at id=1,2,3,4,5,6,7,...
> >
> > The first question I'd have to ask is why you want to do that? If those
> > primary keys are referred to by foreign keys in child tables, you'd have
> to
> > change all those values as well. With a large number of child tables,
> this
> > seems like more work than it's work. Any mistakes and you destroy your
> > database's referential integrity.
> >
> > > Is the vacuum function not the right function?
> >
> > I don't believe so. I think your only recourse is to define another table
> > with the same structure, then select all the records from the old one into
> > the new one, delete the old one, then do the same back again to the old
> > name. Unless there's some pragma or something that allows one to rename a
> > table.
> >
>
>
Re: [sqlite] VACUUM function problem
> I have deleted some objects in my database. Now I have objects at id > (INTEGER PRIMARY KEY) =1,3,4,5,7,8,9,16,17,20 . > id=2,6,10,.. are empty. I want to defrag the database so that I have objects > continuously at id=1,2,3,4,5,6,7,... The first question I'd have to ask is why you want to do that? If those primary keys are referred to by foreign keys in child tables, you'd have to change all those values as well. With a large number of child tables, this seems like more work than it's work. Any mistakes and you destroy your database's referential integrity. > Is the vacuum function not the right function? I don't believe so. I think your only recourse is to define another table with the same structure, then select all the records from the old one into the new one, delete the old one, then do the same back again to the old name. Unless there's some pragma or something that allows one to rename a table.
Re: [sqlite] VACUUM function problem
On Dec 28, 2004, at 11:44 AM, D.W. wrote: Is there a function that does what I have described? [described... 'compact' the primary keys of a database so that there are no unused gaps after a row or rows have been deleted] No. And, generally, you really don't want to do that. The primary keys are generally used to create relationships between tables. That is, the primary key of a row in table A will appear in fields of row or rows in table B (thus called a foreign key). If you were to go and change the primary keys in table A, it would also require changing all of the foreign keys to A in table B (and all other tables). This can be significantly expensive and is one of the reasons why it is generally preferable to keep primary keys meaningless beyond being a unique and unchanging row identifier. If you need a field in table A that acts as a sort of numeric counter of the rows of that table, then create a field to do exactly that and don't use it as a key. in general, I have seen few schemas that actually use such a column of data that requires every number to be consecutive. If order of insertion is important, most schemas use a creation date column. The database representation is generally an INT containing the number of seconds since some significant date; Jan 1, 1970 or 2000 being the most common.With that, you can always sort on said column to determine insert order and the "index" of a particular row in the overall table can generally be implied by the query itself, though not always.
Re: [sqlite] VACUUM function problem
Is there a function that does what I have described? > Defragging the database just removes empty pages making the database > file smaller. It doesn't actually touch any data. > > Have a look here: > http://www.sqlite.org/lang.html#vacuum > > AJ > > On Tue, 28 Dec 2004 17:30:10 +0100, D.W. <[EMAIL PROTECTED]> wrote: > > I have deleted some objects in my database. Now I have objects at id > > (INTEGER PRIMARY KEY) =1,3,4,5,7,8,9,16,17,20 . > > id=2,6,10,.. are empty. I want to defrag the database so that I have objects > > continuously at id=1,2,3,4,5,6,7,... > > Is the vacuum function not the right function? > > Daniel >
Re: [sqlite] VACUUM function problem
Defragging the database just removes empty pages making the database file smaller. It doesn't actually touch any data. Have a look here: http://www.sqlite.org/lang.html#vacuum AJ On Tue, 28 Dec 2004 17:30:10 +0100, D.W. <[EMAIL PROTECTED]> wrote: > I have deleted some objects in my database. Now I have objects at id > (INTEGER PRIMARY KEY) =1,3,4,5,7,8,9,16,17,20 . > id=2,6,10,.. are empty. I want to defrag the database so that I have objects > continuously at id=1,2,3,4,5,6,7,... > Is the vacuum function not the right function? > Daniel
Re: [sqlite] VACUUM function problem
I have deleted some objects in my database. Now I have objects at id (INTEGER PRIMARY KEY) =1,3,4,5,7,8,9,16,17,20 . id=2,6,10,.. are empty. I want to defrag the database so that I have objects continuously at id=1,2,3,4,5,6,7,... Is the vacuum function not the right function? Daniel - Original Message - From: "Ulrik Petersen" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Monday, December 27, 2004 10:40 PM Subject: Re: [sqlite] VACUUM function problem > D.W. wrote: > > >Thanks for your reply. > >I have just checked the version. It's 2.8.15. > >There's no active transaction. > >Do you have another idea? > > > > > How do you verify that nothing happens? > > Ulrik > > -- > Ulrik Petersen, MA, B.Sc. > University of Aalborg, Denmark > >
Re: [sqlite] VACUUM function problem
D.W. wrote: Thanks for your reply. I have just checked the version. It's 2.8.15. There's no active transaction. Do you have another idea? How do you verify that nothing happens? Ulrik -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] VACUUM function problem
Thanks for your reply. I have just checked the version. It's 2.8.15. There's no active transaction. Do you have another idea? > I hope you're using something later than 2.8.0, because VACUUM was only > (re)implemented in 2.8.1. From the docs: > > http://www.sqlite.org/lang.html#vacuum > > "The index or table name argument is now ignored." > > Also from the same page: > > "This command will fail if there is an active transaction. This command > has no effect on an in-memory database." > > HTH > > Ulrik > > -- > Ulrik Petersen, MA, B.Sc. > University of Aalborg, Denmark > >
Re: [sqlite] VACUUM function problem
Hi D.W., D.W. wrote: I use sqlite 2.8.x in combination with PHP. I want to clean up my database and remove empty spaces. I used this command: $ok=sqlite_query($sqlite,"VACUUM tablename"); But it doesnt't work. An error message didn't come either. Does anybody know what is wrong? Daniel I hope you're using something later than 2.8.0, because VACUUM was only (re)implemented in 2.8.1. From the docs: http://www.sqlite.org/lang.html#vacuum "The index or table name argument is now ignored." Also from the same page: "This command will fail if there is an active transaction. This command has no effect on an in-memory database." HTH Ulrik -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
